Re: Syntax bug? Group by?

Mark Woodward wrote:
>> Stephen Frost wrote:
>>
>>> select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15;
>> But back to the query the issue comes in that the ycis_id value is
>> included with the return values requested (a single row value with
>> aggregate values that isn't grouped) - if ycis_id is not unique you will
>> get x number of returned tuples with ycis_id=15 and the same min() and
>> avg() values for each row.
>> Removing the ycis_id after the select will return the aggregate values
>> you want without the group by.
> 
> I still assert that there will always only be one row to this query. This
> is an aggregate query, so all the rows with ycis_id = 15, will be
> aggregated. Since ycis_id is the identifying part of the query, it should
> not need to be grouped.

SELECT ycis_id FROM table WHERE ycis_id=15; returns a single tuple when 
ycis_id is unique otherwise multiple tuples
which means that SELECT ycis_id is technically defined as returning a 
multiple row tuple even if ycis_id is unique - the data in the tuple 
returned is data directly from one table row

SELECT max(col2) FROM table WHERE ycis_id=15; returns an aggregate tuple

SELECT ycis_id FROM table WHERE ycis_id=15 GROUP BY ycis_id; returns an 
aggregate tuple (aggregated with the GROUP BY clause making the ycis_id 
after the SELECT an aggregate as well)

You can't have both a single tuple and an aggregate tuple returned in 
the one statement. If you want the column value of ycis_id in the 
results you need the group by to unify all returned results as being 
aggregates.


-- 

Shane Ambler
Postgres@007Marketing.com

Get Sheeky @ http://Sheeky.Biz