How To Count Null Values With This Query-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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.