Re: Odd subselect in target list behavior WRT aggregation

From: "Mike Mascari" <mascarm(at)mascari(dot)com>
To: "Mike Mascari" <mascarm(at)mascari(dot)com>, "PostgreSQL-General-List (E-mail)" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Odd subselect in target list behavior WRT aggregation
Date: 2003-01-22 19:44:19
Message-ID: 004901c2c24e$a768dc20$0102a8c0@mascari.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)mascari(dot)com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Christoph Dalitz 2003-01-22 19:57:47 Re agregates
Previous Message Andrew Sullivan 2003-01-22 19:28:55 Re: agregates