On Mon, 04 Aug 2014 18:36:24 +0200
RSmith <rsm...@rsweb.co.za> 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
