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

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;

ScaisEdge · Accepted Answer · 2020-05-30 14:56:25Z

0

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

answered May 30, 2020 at 14:56

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1