Cody Robert Cody Robert - 20 days ago 5
MySQL Question

MySQL: Combining two tables with null values does not print 0

In my code I am trying to combine two data tables,

Employee
and
Department
. I tried to write a query to print the respective Department Name and number of employees for all departments, even the unstaffed ones. My query looks like this:

SELECT department.name, count(department.name) AS CountOfNAME
FROM department LEFT JOIN employee ON department.dept_id = employee.dept_id
GROUP BY department.name
ORDER BY Count(department.name) DESC, department.name ASC;


And the result is:

Engineering 5
Recruitment 5
Sales 3
Product 2
Finance 1
Operations 1
Research&Development 1


This code works in that it orders departments by number of employees, and then alphabetically, but
Finance
and
Research&Development
are not supposed to have any people in them. Is there any way to correctly display those results as having 0 employees? It seems to be a hard thing to do in SQL because of how join works.

Answer

The COUNT function should ignore NULL values, giving you a zero count for the finance and research departments. The problem is that you are counting a column in the department table, which always will be non NULL due to that this table is on the left side of the LEFT JOIN. Instead, try counting a column in the employee table:

SELECT department.name,
       COUNT(employee.dept_id) AS CountOfNAME
FROM department
LEFT JOIN employee
    ON department.dept_id = employee.dept_id
GROUP BY department.name
ORDER BY COUNT(employee.dept_id) DESC,
         department.name ASC;