Hello. I have some code which generates subselects in the target list of a query and then aggregates the results. The
codeallows 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.
Isthat correct behavior? The only different between Query #1 and Query #2 is that the second subselect in the target
listof 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@mascari.com