Odd subselect in target list behavior WRT aggregation
От | Mike Mascari |
---|---|
Тема | Odd subselect in target list behavior WRT aggregation |
Дата | |
Msg-id | 001501c2c245$baf14060$0102a8c0@mascari.com обсуждение исходный текст |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: