Run
Run

Reputation: 896

Why sqlite return null for count(*) with group by query?

If I do simple query, for:

select count(*) from emails
where id <= 0

It returns one row of zeros.

However, if I group by the query, for:

select count(*) from emails
where id <= 0
group by emails.id

It returns no rows.

Why then behave differently and how to modify the second query to achieve the same behavior of the first one?

The command line ouputs:

sqlite> select count(*) from emails where id <= 0;
0
sqlite> select count(*) from emails where id <= 0 group by emails.id;
sqlite> 

Upvotes: 0

Views: 1072

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Your first query is an aggregation query with no group by:

select count(*)
from emails
where id <= 0;

Such a query is always guaranteed to return exactly one row. The entire set of rows is treated as a single group, even when the table is empty or all rows are filtered out. When this happens COUNT() returns 0. Most other aggregation functions (perhaps all of them) return NULL. COUNT() never returns NULL.

Your second query:

select count(*)
from emails
where id <= 0
group by emails.id;

is an aggregation query with a GROUP BY. Such a query returns one row for each set of values described by the GROUP BY keys. In this case, there are no groups because all rows are filtered out. Hence, this returns no rows at all.

Upvotes: 4

Serg
Serg

Reputation: 22811

This is because no rows exists with id<=0 so it's exactly 0 rows in a table matching id<=0 and no rows to show when trying to group by id.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521987

Use conditional aggregation:

select count(case when id <= 0 then 1 end) as cnt
from emails;

The problem with the WHERE clause is that should no record at all have id <= 0, there would be nothing in the result set.

Upvotes: 0

Related Questions