Odd subselect in target list behavior WRT aggregates

From: "Mike Mascari" <mascarm(at)mascari(dot)com>
To: "PostgreSQL-development" <pgsql-hackers(at)postgresql(dot)org>
Subject: Odd subselect in target list behavior WRT aggregates
Date: 2003-01-24 06:09:17
Message-ID: 002a01c2c36f$20e10840$0102a8c0@mascari.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello. I have some code which generates subselects in the target
list of a query and then aggregates the results. The code allows
the user to determine the attributes of the aggregation. If the
user chooses to aggregate on the same value twice, I get the
"Sub-SELECT" error. If the user chooses a different second
attribute of aggregation, no error occurs. Is that correct
behavior? The only difference between Query #1 and Query #2 is
that the second subselect in the target list of Query #2
aggregates on the 'day' of a sale as opposed to the 'hour':

Query #1
--------

SELECT SUM(p.dstqty) as agg,
(SELECT date_trunc('hour', sales.active)
FROM sales
WHERE p.purchase = sales.purchase) as field1,
(SELECT date_trunc('hour', sales.active)
FROM sales
WHERE p.purchase = sales.purchase) as field2

FROM purchases p
WHERE ...
GROUP BY 2,3;

ERROR: Sub-SELECT uses un-GROUPed attribute p.purchase from
outer query

Query #2
--------

SELECT SUM(p.dstqty) as agg,
(SELECT date_trunc('hour', sales.active)
FROM sales
WHERE p.purchase = sales.purchase) as field1,
(SELECT date_trunc('day', sales.active)
FROM sales WHERE p.purchase = sales.purchase) as field2

FROM purchases p
WHERE ...
GROUP BY 2,3;

agg | field1 | field2
-------+------------------------+------------------------
1.0000 | 2002-12-27 18:00:00-05 | 2002-12-27 00:00:00-05

I also failed to mention in the original post that this is
PostgreSQL version 7.2.1.

Any help or instruction would be greatly appreciated.

Mike Mascari
mascarm(at)mascari(dot)com

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2003-01-24 07:17:56 Re: Release Scheduales: 7.2.4 & 7.3.2
Previous Message Justin Clift 2003-01-24 05:42:33 Re: Release Scheduales: 7.2.4 & 7.3.2