I have two tables in mysql in which i want to query in mysql as follows

Question

table - emp

ename | sal  | deptno
-----------------------
smith | 800  | 20
allen | 1600 | 30
ward  | 1400 | 30
jones | 1200 | 20

---

table - dept

deptno | dname
----------------
20     | Accounting
30     | Sales

i want the following output which displays average salary of each department

deptno| dname     | avg(sal)
-----------------------------
20    | Accounting| 1000
30    | Sales     | 1500

i have tried the query select emp.deptno, dname, avg(sal) from emp,dept group by (emp.deptno);

but it is showing error dept.dname' which is not functionally dependent on columns in GROUP BY clause;

Answer 1

just use avg() and group by

select  a.deptno, a.dname, avg(b.sal)
from dept a 
inner  join emp b on a.deptno = b.deptno
group by   a.deptno, a.dname