Menu
I am trying to get a count of each value in a table using the following SQL:
SELECT col, COUNT(col)
FROM table
GROUP BY col
(There's a WHERE clause in the real code, but it has no impact).
When I run this I get results like so:
a - 5
b - 4
<null> - 0
It doesn't matter how many null entries I have, it always shows a count of 0.
Any ideas why?
972
GROUP BY does treat all NULL values equally.
Group functions ignore the NULL values in the column. To enforce the group functions ti include the NULL value, use NVL function.
COUNT(expression) returns the number of values in expression, which is a table column name or an expression that evaluates to a column of data. COUNT(expression) does not count NULL values. This query returns the number of non-NULL values in the Name column of Sample.
Use count(*) : select count(*) from train where "column" is NULL; count() with any other argument counts the non-NULL values, so there are none if "column" is NULL . Save this answer.
Figured it out. Changed the code to use COUNT(*) instead of COUNT(col).
COUNT(col) was not counting any null rows, all other aggregation methods also eliminate nulls from the result set.
71
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
