> 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.
>> My question, is it a syntactic technicality that PostgreSQL asks for a
>> "group by," or a bug in the parser?
> AFAIK what you want is not per sql spec. What if you had instead written
>   select ycis_id, min(tindex), avg(tindex) from y where frobnitz(ycis_id)
> = 15;
> ? I think you are expecting too much reasoning from the engine.
Regardless, I can get the results I need and have already worked around
this. The reason why I posted the question to hackers was that I think it
is a bug.

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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?


Reply via email to