How can I group by on a field which has NULL values?

Question

I have a table with 2 fields

 x          y
----       ----
 1         null            
 2          5
 3          5
 4         null
 5         null
 6          10
 7          5

And my SQLite query is

select y,count(y)
from mytable
group by y

And the result is

null    0
 5      3
 10     1

It is expected to see null 3.
But the output is null 0.
what does it mean?

Answer 1

From Aggregate Functions in SQLite

The count(X) function returns a count of the number of times that X is not NULL in a group. The count(*) function (with no arguments) returns the total number of rows in the group.

So, the COUNT function does not count NULL so use COUNT(*) instead of COUNT(y).

SELECT y, COUNT(*) AS COUNT
FROM mytable
GROUP BY y

Or you can also use COUNT(x) like this one.

SELECT y, COUNT(x) AS COUNT
FROM mytable
GROUP BY y

See this SQLFiddle