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(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz