Re: Syntax bug? Group by?

Joe Sunday wrote:
> On Tue, Oct 17, 2006 at 02:41:25PM -0400, Mark Woodward wrote:
> 
>> The output column "ycis_id" is unabiguously a single value with regards to
>> the query. Shouldn't PostgreSQL "know" this? AFAIR, I think I've used this
>> exact type of query before either on PostgreSQL or another system, maybe
>> Oracle, and it did work.
> 
> Doesn't work in Oracle 10g:
> 
> SELECT ycis_id, tindex from x where ycis_id = 15;
> YCIS_ID  TINDEX
> =======  ======
>      15      10
>      15      20
> 
> SELECT ycis_id, min(tindex), avg(tindex) from x where ycis_id = 15;
> ORA-00937: not a single-group group function
> 
> SELECT ycis_id, min(tindex), avg(tindex) from x where ycis_id = 15 GROUP BY ycis_id;
> YCIS_ID  MIN(TINDEX)  AVG(TINDEX)
> =======  ===========  ===========
>      15           10           15
> 
> --Joe
> 

MySQL reports -
Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns 
is illegal if there is no GROUP BY clause


I found one that actually returns the desired result - SQLite3.

sqlite> select * from test;
15|20
15|10
sqlite> select ycis_id,min(tindex),avg(tindex) from test where ycis_id=15;
15|10|15
sqlite>


-- 

Shane Ambler
Postgres@007Marketing.com

Get Sheeky @ http://Sheeky.Biz