Thread: select count(*) from hits group by count;
Hi there,
I have a table hits:
discovery=> \d hits
Table = hits
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| msg_id | int4 not null | 4 |
| count | int4 not null | 4 |
| first_access | datetime default now ( ) | 8 |
| last_access | datetime | 8 |
+----------------------------------+----------------------------------+-------+
Index: hits_pkey
and query:
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 ?
Last snapshot also behave like 6.5.3
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 ?
Mysql seems allows this ?
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
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
On 29-Jan-2000 Oleg Bartunov wrote: > Hi there, > and query: > 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 ? > Last snapshot also behave like 6.5.3 > > > 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 AFAIK, MS SQL, Sybase and Informix have the same restrictions, because using all fields in query with aggregates and grouping is meaningless. Order of fields in group by clause also significant. -- Dmitry Samersoff, dms@wplus.net, ICQ:3161705 http://devnull.wplus.net * There will come soft rains ...