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


Sat, 29 Jan 2000 12:39:42 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
select count(*)  from hits group by count;
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 column of 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

Sat, 29 Jan 2000 11:24:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] select count(*) from hits group by count; 

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 ...

Sat, 29 Jan 2000 19:40:04 +0300 (MSK)
Dmitry Samersoff <dms@wplus.net>
RE: [HACKERS] select count(*)  from hits group by count;