How can I group a field into a field that is null?

Question

I have a table with 2 fields

xy ---- ---- 1 null 2 5 3 5 4 null 5 null 6 10 7 5 

And my SQLite query

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

And the result

 null 0 5 3 10 1 

NULL 3 expected.
But the output is zero.
what does it mean?

Answer 1

From aggregate functions in SQLite

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

So, the COUNT function does not take NULL into account, 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

Answer 2

You can use isnull in SQL, which gives all null values ​​for another identifier

Just run the query below, you will get the desired results

 Select y,count(isnull(y,-1)) as [Count] from mytable group by y