I'm not 100% sure if the devs had another reason for doing it this way , but the results are very much expected in normal SQL terms
- simply because "count()" returns a value which is sensible and represents the actual number of items in the ungrouped list... the
fact that the value returned is Zero has nothing to do with the concept, it still returns a value and as such forms part of a valid
record line. If however you add the GROUP BY clause, then there is no valid group to return a count of... and hence no line.
You don't even need all those example DBs, can just do this:
CREATE TABLE x(id INTEGER);
SELECT Count(*) FROM x;
| 0 |
--vs.--
SELECT Count(*) FROM x GROUP BY id;
(No Results)
Paints a clear picture I hope!
On 2014/08/04 17:47, Mark Lawrence wrote:
I try to remember to define GROUP BY values when using aggregate
functions (and I wish SQLite considered it an error otherwise) but I
forget once and the result surprised me.
CREATE TABLE x(
id INTEGER
);
CREATE TABLE y(
id INTEGER
);
SELECT
x.id,
count(y.id)
FROM
x
INNER JOIN
y
ON
y.id = x.id
ORDER BY
x.id
;
Result:
id count(y.id)
---------- -----------
NULL 0
It is expected behaviour that a row is returned in this situation even
through the tables are empty? Adding a "GROUP BY x.id" returned the
expected empty set.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
