3

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?

  • Which database? Could it be a corruption in the index file? can you optimize the table, then rerun the query please? i.e. if MySQL, run 'OPTIMIZE TABLE gb;'. Not sure about other RDBMS, but you can try: CREATE TABLE gb1 LIKE gb; INSERT INTO gb1 SELECT * FROM gb; then try again. – Jehad Keriaki Aug 14 '15 at 15:22
5

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.