Oleg Bartunov <oleg@sai.msu.su> writes:
> discovery=> select count(*) from hits group by count;
> ERROR: Aggregates not allowed in GROUP BY clause
> There's easy workaround :
> discovery=> select count(*) as qq from hits group by count;
> but I'm curious is this a valid query ?
I believe this is probably a bug. We are treating GROUP BY the same
way we treat ORDER BY, namely that if an item is a simple name or
integer constant, we try first to interpret it as a result-column
name or number; only if it does not match any column name do we fall
back on treating it as a general expression. And the default result-
column name for "count(*)" is just "count".
This behavior is necessary to conform to the standard for ORDER BY ---
in fact, SQL92 doesn't actually allow anything *but* a result-column
name or number for ORDER BY. Accepting an expression is a Postgres
extension (I imagine other DBMSs do it too).
But I can't see anything in the spec that justifies treating a GROUP BY
item that way: a GROUP BY item is defined as a <column reference> which
is a plain expression constituent. We should probably change the code
behavior so that GROUP BY is always interpreted as a normal expression.
Question is, how many existing apps might be broken by such a change?
> Another question:
> discovery=> select count(*) as qqq,* from hits group by last_access;
> produces error:
> ERROR: Illegal use of aggregates or non-group column in target list
> Do I really need to have all fields in GROUP clause ?
Yes. See SQL92 7.9(7):
7) If T is a grouped table, then each <column reference> in each <value expression> that references a
columnof T shall refer- ence a grouping column or be specified within a <set function
specification>.
> Mysql seems allows this ?
Mysql is broken if it accepts this. There's no unique answer to give
for an ungrouped, non-aggregated column.
regards, tom lane