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