Thread: Odd subselect in target list behavior WRT aggregation

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

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