How to know the count of null values on a specific column?-CodePudding

I am having an error message saying that my select clause is not specified in a GROUP BY. But my other, somewhat similar, line of statements/conditions seems fine without it.

-- How many rows has empty middle initial?

SELECT workDept, COUNT(midInit)
FROM emp
WHERE midInit IS NULL
ORDER BY workDept;

CodePudding user response：

Would something like this work? This adds a GROUP BY to sum for each dept, and provides a count for all rows, as well as a count for when the midInit is null.

SELECT workDept, 
count(*) as allEmps
SUM(CASE WHEN midInit is null THEN 1 ELSE 0 END) as countNulls
FROM emp
GROUP BY workDept
ORDER BY workDept;

CodePudding user response：

try use case when like this

    count(case when midInit IS NULL then 1 else 0 end)