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@mascari.com