Re: [sqlite] Expected behaviour of COUNT with no GROUP BY?
On Mon, 04 Aug 2014 18:36:24 +0200 RSmith wrote: > > I guess I still find the combination of COUNT > > without a GROUP BY to be unintuitive, but at least I know why now. > > As far as I know, there is no requirement for a group by clause for > any of the functions really, a table or any SELECT result set is by > definition "a group" (or should I say "the Group"), and unless a > "group by" is explicitly stated, the table/result-set is itself the > group and all group functions (aggregates functions) should work on > it. Depends on what you mean by "no requirement". It may help to remember the that "the group" is the set of *columns* that constitute the argument to the SELECT operator. When you say, select count(*) from T no columns are mentioned. There is no group (or, the group is empty), and the count refers to the number of rows that meet the WHERE criteria (in this case, all of them). When you say, select count(*), A from T group by A the meaning is the count of rows in T for each unique A. SQL *requires* the columns to be restated in the GROUP BY clause. I think you're implying the language could have been unambiguously defined without GROUP BY because the required information is present in the SELECT column-set. That's true, but the language for which there's "no requirement" is not the SQL currently defined. When you say, select count(*), A from T we have now left the reservation. The OP may find SQLite's behavior in this regard unintuitive because it is illogical. Consider this table, sqlite> select * from T; A -- 1 2 IIRC Sybase 20 years ago would accept the above query and produce select count(*), A from T; count(*)A -- -- 2 1 2 2 which makes a little sense: here are the values of A you asked for, and the count of T you asked for. In modern terms, select A, q from T cross join (select count(*) as q from A) as Q But SQLite sort of punts, sqlite> select count(*), A from T; count(*)A -- -- 2 2 i.e., here's an arbitrary value of A and the count of T. It doesn't make sense because it doesn't make sense. I'm sure there are applications that depend on the current, documented behavior. I'm equally sure there's a constituency that would favor either standard behavior or (better) making GROUP BY optional and producing an error only when it disagrees with SELECT. Perhaps there is room for that in version 4. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Expected behaviour of COUNT with no GROUP BY?
On 8/4/14, 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. > > 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. This is the behavior I expect from the documentation. Specifically, the documentation for the SELECT statement at "http://sqlite.org/lang_select.html"; says > If the SELECT statement is an aggregate query without a GROUP BY clause, then > each aggregate expression in the result-set is evaluated once across the > entire dataset. [...] Or, if the dataset contains zero rows, then each > non-aggregate expression is evaluated against a row consisting entirely of > NULL values. > > [...] An aggregate query without a GROUP BY clause always returns exactly one > row of data, even if there are zero rows of input data. -- Ambrus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Expected behaviour of COUNT with no GROUP BY?
On 2014/08/04 18:27, Mark Lawrence wrote: On Mon Aug 04, 2014 at 06:04:53PM +0200, RSmith wrote: Yes it does, thanks. I guess I still find the combination of COUNT without a GROUP BY to be unintuitive, but at least I know why now. As far as I know, there is no requirement for a group by clause for any of the functions really, a table or any SELECT result set is by definition "a group" (or should I say "the Group"), and unless a "group by" is explicitly stated, the table/result-set is itself the group and all group functions (aggregates functions) should work on it. The reverse is not necessarily true though, in that SQL (the Standard anyway) really requires you to specify an aggregate function for each and every element in a SELECT containing a GROUP BY function - but most engines (SQLite too) are quite lenient in this regard. (This being my current understanding and experience, I might be wrong) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Expected behaviour of COUNT with no GROUP BY?
On Mon Aug 04, 2014 at 06:04:53PM +0200, RSmith wrote: > 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! Yes it does, thanks. I guess I still find the combination of COUNT without a GROUP BY to be unintuitive, but at least I know why now. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Expected behaviour of COUNT with no GROUP BY?
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) -- --- NULL0 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
Re: [sqlite] Expected behaviour of COUNT with no GROUP BY?
On Mon, Aug 4, 2014 at 11:47 AM, Mark Lawrence wrote: > > It is expected behaviour that a row is returned in this situation even > through the tables are empty? > Yes. All SQL database engines work that way. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Expected behaviour of COUNT with no GROUP BY?
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) -- --- NULL0 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. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
