CODEWITHSUNDEEP
mysql
VikramK
VikramK

Reputation: 663

MySQL: Multiple columns for group clause

I want to show number of employees in each role in each department.For this I have created below mentioned query. But it does not give (a) ALL the roles (b) All the employees in role in respective department where employee count is zero.

SELECT qodi.department_name,IFNULL(qobi.band_name, 'No Band') 'Band',count(qu1.user_id) emp_count
FROM 
qee_org_dept_info qodi
LEFT OUTER JOIN qee_user qu1 ON qodi.department_name =qu1.department AND qu1.org_id=1
LEFT OUTER JOIN qee_org_band_info qobi ON qobi.band_name = qu1.band_name
GROUP BY qodi.department_name,qobi.band_name
ORDER BY qodi.department_name

Roles are defined in qee_org_band_info. Users are defined in qee_user and departments in qee_org_dept_info

Result of above query is:- enter image description here

Expected Results is :- All departments and all roles should come, even if employee count for that role in respective department is zero.

Upvotes: 1

Views: 41

Answers (1)

Shadow
Shadow

Reputation: 34243

All departments and all roles should come, even if employee count for that role in respective department is zero.

This means, that you need to produce a cartesian join between the departments and roles and join the employees on the result of the cartesian join.

SELECT qodi.department_name,IFNULL(qobi.band_name, 'No Band') 'Band',count(qu1.user_id) emp_count
FROM 
(qee_org_dept_info qodi INNER JOIN qee_org_band_info qobi)
LEFT OUTER JOIN qee_user qu1 ON qodi.department_name =qu1.department AND qobi.band_name = qu1.band_name AND qu1.org_id=1 
GROUP BY qodi.department_name,qobi.band_name
ORDER BY qodi.department_name

Pls note that I changed the order of the joins and that I used inner join instead of left to join departments with bands. According to mysql documentation:

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

Technically, there is no need to have parentheses around the inner join expression, however, I feel that the code is more readable this way.

Upvotes: 1

Related Questions