Odd subselect in target list behavior WRT aggregates
От | Mike Mascari |
---|---|
Тема | Odd subselect in target list behavior WRT aggregates |
Дата | |
Msg-id | 002a01c2c36f$20e10840$0102a8c0@mascari.com обсуждение исходный текст |
Ответы |
Re: Odd subselect in target list behavior WRT aggregates
|
Список | pgsql-hackers |
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 salesWHERE p.purchase = sales.purchase) as field1, (SELECT date_trunc('hour', sales.active)FROM salesWHERE 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 salesWHERE 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
В списке pgsql-hackers по дате отправления: