Re: Syntax bug? Group by?

Stephen Frost wrote:
> * Mark Woodward (pgsql@mohawksoft.com) wrote:
>> If I am asking for a specific column value, should I, technically
>> speaking, need to group by that column?
> 
> Technically speaking, if you're asking for a specific tuple, should you
> be allowed to request an aggregation?
> 
Only with the assumption that the value in the where clause is for a 
unique column.

If you want min(col2) and avg(col2) where col1=x you can get it without 
a group by, the same as if you put col1<x - if you want an aggregate of 
all records returned not the aggregate based on each value of col1.


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


-- 

Shane Ambler
Postgres@007Marketing.com

Get Sheeky @ http://Sheeky.Biz