Extra "count" row with total of 0

Consider the following:

db=> SELECT ga, count(ga) FROM gb GROUP BY ga ORDER BY ga ;

which gives the following output:

ga | count
---+------
?  |  200
A  |  100
B  |   50
   |    0
(4 rows)

Why is there an additional row with an empty ga and a count of zero?

Why is there an additional row with an empty ga and a count of zero?

Because there are NULL values in the ga column.


Why the count is zero?

While COUNT(*) can never give 0 as a result (as it counts rows), COUNT(column) or COUNT(expression) can give 0 because it counts not null values in the column/expression.

Thus the only logical explanantion is that the ga column has some nulls.


How many?

You can find out by running:

SELECT ga, 
       count(ga) AS count_non_nulls,
       count(*)  AS count_all
FROM gb 
GROUP BY ga 
ORDER BY ga ;

which will give you something like:

ga | count_non_nulls | count_all
---+-----------------+-----------
?  |  200            |  200
A  |  100            |  100
B  |   50            |   50
   |    0            |   17  -- some number >= 1 
(4 rows)

StackExchange Database Administrators Q#111069, answer score: 5