Odd subselect in target list behavior WRT aggregation

From: "Mike Mascari" <mascarm(at)mascari(dot)com>
To: "PostgreSQL-General-List (E-mail)" <pgsql-general(at)postgresql(dot)org>
Subject: Odd subselect in target list behavior WRT aggregation
Date: 2003-01-22 18:40:26
Message-ID: 001501c2c245$baf14060$0102a8c0@mascari.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Any help or instruction would be greatly appreciated.

Mike Mascari
mascarm(at)mascari(dot)com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message codeWarrior 2003-01-22 18:41:11 Re: agregates
Previous Message Justin Clift 2003-01-22 18:19:17 C++ coding assistance request for a visualisation tool