Reputation: 896
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
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
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
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