Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 |
+--------------+----------+----------+
like image 902
Maruli Avatar asked Apr 09 '26 23:04

Maruli


1 Answers

Try with this:

   select d.DepartmentID,g.ID, COUNT(e.ID) AS TotalEmp
   from Gender g
   cross join Department d
   left join Employee e on e.genderid=g.id and e.departmentid=e.DepartmentID
   group by d.DepartmentID,g.id;
like image 59
Sanghita Avatar answered Apr 11 '26 15:04

Sanghita



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!