Re: Syntax bug? Group by?

Stephen Frost wrote:
> * Mark Woodward (pgsql(at)mohawksoft(dot)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(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz