I'm messing around in my school's database and I am trying to count the null values in this query:
SELECT Depart_ID, DepartName,
COUNT(Name) AS employees,
SUM(Salary) AS 'Total salary'
FROM Ch4Employee AS E
INNER JOIN Ch4Department AS D
ON E.Depart_ID = D.ID
GROUP BY Depart_ID, DepartName
Any tips?
If you want to count NULL salaries, do count(*) - count(salary), because count(*) counts all rows, and count(salary) counts the non-null values.
E.g.
SELECT Depart_ID, DepartName,
COUNT(Name) AS employees,
SUM(Salary) AS 'Total salary',
COUNT(*) - COUNT(Salary) AS null_salaries
FROM Ch4Employee AS E
INNER JOIN Ch4Department AS D
ON E.Depart_ID = D.ID
GROUP BY Depart_ID, DepartName
select Depart_ID, DepartName, COUNT(Name) as employees, SUM(Salary) as 'Total salary' from Ch4Employee AS E inner join Ch4Department as D on E.Depart_ID = D.ID group by Depart_ID, DepartName HAVING COUNT(Name) = 0
If you want to check the values after aggregation you have to use Having.
