Maruli Maruli - 4 months ago 8
SQL Question

Retrieve COUNT as 0 when JOIN doesn't return value

I have following tables

Gender

+----+-------------+
| ID | Description |
+----+-------------+
| 1 | M |
| 2 | F |
+----+-------------+


Department

+----+-------------------+
| ID | DepartmentName |
+----+-------------------+
| 1 | Application |
| 2 | Change Management |
| 3 | Infrastructure |
+----+-------------------+


Employee

+----+----------+----------+-------------+--------------+
| ID | Name | GenderID | StaffNumber | DepartmentID |
+----+----------+----------+-------------+--------------+
| 1 | Stephen | 1 | SC001 | 1 |
| 2 | Jacob | 1 | SC002 | 1 |
| 3 | Maria | 2 | SC003 | 1 |
| 4 | Valerie | 2 | SC004 | 1 |
| 5 | Herman | 1 | SC005 | 3 |
| 6 | Trevor | 1 | SC006 | 3 |
| 7 | Mark | 1 | SC007 | 2 |
| 8 | Hendrick | 1 | SC008 | 2 |
+----+----------+----------+-------------+--------------+


I want to find out the total number of employees grouped by Deparment & Gender. If a gender does not exist for a department, I want to still show the gender with 0 as the count.

I tried this SQL but it doesn't retrieve what I want:

SELECT
e.DepartmentID,
e.GenderID,
COUNT(e.ID) AS TotalEmp
FROM
Employee e
RIGHT OUTER JOIN Gender g
ON e.GenderID = g.ID
GROUP BY
e.DepartmentID,
e.GenderID
ORDER BY
e.DepartmentID,
e.GenderID


Current Result

+--------------+----------+----------+
| DepartmentID | GenderID | TotalEmp |
+--------------+----------+----------+
| 1 | 1 | 2 |
| 1 | 2 | 2 |
| 2 | 1 | 2 |
| 3 | 1 | 2 |
+--------------+----------+----------+


Expected Result

+--------------+----------+----------+
| DepartmentID | GenderID | TotalEmp |
+--------------+----------+----------+
| 1 | 1 | 2 |
| 1 | 2 | 2 |
| 2 | 1 | 2 |
| 2 | 2 | 0 |
| 3 | 1 | 2 |
| 3 | 2 | 0 |
+--------------+----------+----------+

Answer

Try with this:

   select d.DepartmentID,g.GenderID, COUNT(e.ID) AS TotalEmp
   from Gender g
   cross join Department d
   left join Employee e on e.genderid=g.genderid and e.departmentid=e.DepartmentID
   group by d.DepartmentID,g.GenderID;