CODEWITHSUNDEEP
mysql
Fawzan
Fawzan

Reputation: 4849

Inner join on multiple tables produces error

I have three SQL tables and I am trying to get the list of department id, average employee age, the range of employee salary(min-max) and the total number of employees as a table. 

Emp ( eid : INTEGER, ename: STRING; age: INTEGER; salary : REAL ) 
Works ( eid: INTEGER, did: INTEGER, pct_time: INTEGER ) 
Dept ( did: INTEGER, budget: real, manager_id: INTEGER )

I am using the left join to find the total number of employees in each department as following and it works. 

SELECT d.did, COUNT(e.eid) AS total_employees 
FROM works d LEFT JOIN works e ON e.did = d.did
GROUP BY d.did;

But when I try to find the minimum salary for the department MySQL gives an error. 

SELECT w.did, COUNT(e.eid) AS total_employees, e.salary
FROM works w LEFT JOIN emp e ON e.eid = w.eid
GROUP BY w.did;

ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sqlw1.e.salary' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by mysql> SELECT w.did, COUNT(e.eid) AS total_employees, e.salary FROM works w LEFT JOIN emp e ON e.eid = w.eid

Can someone tell me what's the mistake of trying to access the salary here? Also, I'd appreciate your comment on how I can join these three tables to obtain the column values.

Upvotes: 1

Views: 257

Answers (2)

Siva
Siva

Reputation: 1529

You have to find the MIN salary of employee so you need to group by employee id,

Try with below query.

SELECT w.did, COUNT(e.eid) AS total_employees, MIN(e.salary) AS salary
FROM works w LEFT JOIN emp e ON e.eid = w.eid
GROUP BY w.eid;

Upvotes: 0

DEarTh
DEarTh

Reputation: 1005

In this type of query either you have to put column into group by or you have to use some aggregate function on column.

You need to change your query to 

SELECT w.did, COUNT(e.eid) AS total_employees, MIN(e.salary)
FROM works w LEFT JOIN emp e ON e.eid = w.eid
GROUP BY w.did;

Here we are using MIN() function to fetch the minimum e.salary

Upvotes: 1

Related Questions