Thread: sum of agreggates in one SELECT?
Hello, I have the following query/result: auction=# select max(b.lot) as quantity,max(b.price) as price,p.login from bid b, person p where b.auction_id = 84 and p.id= b.person_id group by p.login order by max(price);quantity | price | login ----------+-------+----------- 1 | 5000 | papy 12 | 5750 | cunctator 8 | 6000 | vindex (3 rows) Now I would like to sum() all results from the quantity column and return it with one SELECT statement. Is that possible? I am trying: auction=# select sum(b.lot) from bid b, person p where b.auction_id = 84 and p.id = b.person_id ;sum ----- 52 (1 row) But this is wrong because it sums all quantities. I don't know how to apply a valid WHERE clause in that case. Thanks in advance for any help, cheers, -- Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org
Could it be that the first query is using max which will only return the max for b.lot and b.price.. so maybe. select sum(max(b.lot))as quantity, max(b.price) as price from bid b, person p where b.auction_id = 84 and p.id = b.person_id ; Just a thought. -----Original Message----- From: pgsql-sql-owner@hub.org [mailto:pgsql-sql-owner@hub.org]On Behalf Of Louis-David Mitterrand Sent: Tuesday, September 19, 2000 3:59 AM To: pgsql-sql@postgresql.org Subject: [SQL] sum of agreggates in one SELECT? Hello, I have the following query/result: auction=# select max(b.lot) as quantity,max(b.price) as price,p.login from bid b, person p where b.auction_id = 84 and p.id = b.person_id group by p.login order by max(price);quantity | price | login ----------+-------+----------- 1 | 5000 | papy 12 | 5750 | cunctator 8 | 6000 | vindex (3 rows) Now I would like to sum() all results from the quantity column and return it with one SELECT statement. Is that possible? I am trying: auction=# select sum(b.lot) from bid b, person p where b.auction_id = 84 and p.id = b.person_id ;sum ----- 52 (1 row) But this is wrong because it sums all quantities. I don't know how to apply a valid WHERE clause in that case. Thanks in advance for any help, cheers, -- Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org
Louis-David Mitterrand <cunctator@apartia.ch> writes:
> Now I would like to sum() all results from the quantity column and
> return it with one SELECT statement. Is that possible?
You can't have aggregates of aggregates in a single SELECT structure;
that's just not in the SQL execution model. The way around this is
to write multiple levels of SELECT, using either selection from a
grouped/aggregated view or subselect-in-FROM. Unfortunately Postgres
doesn't have either of those features --- yet. They might be in 7.1
if I spend less time answering email and more time coding...
regards, tom lane
Tom, > You can't have aggregates of aggregates in a single SELECT structure; > that's just not in the SQL execution model. The way around this is > to write multiple levels of SELECT, using either selection from a > grouped/aggregated view or subselect-in-FROM. Unfortunately Postgres > doesn't have either of those features --- yet. They might be in 7.1 > if I spend less time answering email and more time coding... Well, stop answering your e-mail, then, dammit! (impatiently waiting for 7.1) Seriously, you could actually slack (as in wait 8-12 hours) on answering the questions. There's been several basic SQL questions I could have fielded and you answered them first. Unfortunately, Louis-David, I don't see any way around subselects in the FROM clause as Tom mentions, which are not currently supported. I'd suggest using a Function to create a temporary table or view and summarizing from that. -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Well, it's not a single SELECT, but why not use something like: SELECT MAX(b.lot) AS quanity, max(p.price) AS price, p.login INTO TEMPORARY TABLE temp1 FROM bid b, person p WHERE b.auction_id=84 AND p.id=b.person_id GROUP BY p.login ORDER BY max(price); SELECT SUM(quanity) from temp1; If you need the output from the original SELECT then you can print it by simply doing: SELECT * FROM temp1; Hope this is of some use to you, John On Tue, 19 Sep 2000, Louis-David Mitterrand wrote: > Hello, > > I have the following query/result: > > auction=# select max(b.lot) as quantity,max(b.price) as price,p.login from bid b, person p where b.auction_id = 84 and p.id = b.person_id group by p.login order by max(price); > quantity | price | login > ----------+-------+----------- > 1 | 5000 | papy > 12 | 5750 | cunctator > 8 | 6000 | vindex > (3 rows) > > Now I would like to sum() all results from the quantity column and > return it with one SELECT statement. Is that possible? > > I am trying: > > auction=# select sum(b.lot) from bid b, person p where b.auction_id = 84 and p.id = b.person_id ; > sum > ----- > 52 > (1 row) > > But this is wrong because it sums all quantities. I don't know how to > apply a valid WHERE clause in that case. > > Thanks in advance for any help, cheers, > > >
On Tue, Sep 19, 2000 at 01:17:01PM -0500, John McKown wrote: > Well, it's not a single SELECT, but why not use something like: > > SELECT MAX(b.lot) AS quanity, max(p.price) AS price, p.login > INTO TEMPORARY TABLE temp1 > FROM bid b, person p > WHERE b.auction_id=84 AND p.id=b.person_id > GROUP BY p.login > ORDER BY max(price); > > SELECT SUM(quanity) from temp1; > > If you need the output from the original SELECT then you can print it by > simply doing: > > SELECT * FROM temp1; > > Hope this is of some use to you, Very useful, as it demonstrates that (as in perl) there is sometimes more than one way to do it. Your solution works fine, and along the way I learned to use temporary tables. Thanks a lot for your input, cheers, -- Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org How's my posting? Call 1-800-DEV-NULL
On Tue, Sep 19, 2000 at 11:06:06AM -0700, Josh Berkus wrote:
>
> Unfortunately, Louis-David, I don't see any way around subselects in the
> FROM clause as Tom mentions, which are not currently supported. I'd
> suggest using a Function to create a temporary table or view and
> summarizing from that.
I did create a pl/pgsql function in the end, to compute my total:
FOR bid IN SELECT max(b.lot) AS price FROM bid b WHERE b.auction_id = $1 GROUP BY b.person_id
LOOP i := i + bid.price; END LOOP; RETURN i;
Thanks for your input,
--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.org
"Faith strikes me as intellectual laziness." -- Robert A. Heinlein