pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
sum of agreggates in one SELECT?
6+ messages / 4 participants
[nested] [flat]

* sum of agreggates in one SELECT?
@ 2000-09-19 10:58  Louis-David Mitterrand <cunctator@apartia.ch>
  0 siblings, 3 replies; 6+ messages in thread

From: Louis-David Mitterrand @ 2000-09-19 10:58 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* RE: sum of agreggates in one SELECT?
@ 2000-09-19 15:28  Stuart Foster <stuartafoster@home.com>
  parent: Louis-David Mitterrand <cunctator@apartia.ch>
  2 siblings, 0 replies; 6+ messages in thread

From: Stuart Foster @ 2000-09-19 15:28 UTC (permalink / raw)
  To: ldm@apartia.ch; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: sum of agreggates in one SELECT?
@ 2000-09-19 17:27  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Louis-David Mitterrand <cunctator@apartia.ch>
  2 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2000-09-19 17:27 UTC (permalink / raw)
  To: ldm@apartia.ch; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: sum of agreggates in one SELECT?
@ 2000-09-19 18:17  John McKown <joarmc@swbell.net>
  parent: Louis-David Mitterrand <cunctator@apartia.ch>
  2 siblings, 1 reply; 6+ messages in thread

From: John McKown @ 2000-09-19 18:17 UTC (permalink / raw)
  To: ldm@apartia.ch; +Cc: pgsql-sql

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,
> 
> 
> 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: sum of agreggates in one SELECT?
@ 2000-09-19 20:06  Louis-David Mitterrand <cunctator@apartia.ch>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Louis-David Mitterrand @ 2000-09-19 20:06 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: sum of agreggates in one SELECT?
@ 2000-09-20 06:44  Louis-David Mitterrand <cunctator@apartia.ch>
  parent: John McKown <joarmc@swbell.net>
  0 siblings, 0 replies; 6+ messages in thread

From: Louis-David Mitterrand @ 2000-09-20 06:44 UTC (permalink / raw)
  To: John McKown <joarmc@swbell.net>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2000-09-20 06:44 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-09-19 10:58 sum of agreggates in one SELECT? Louis-David Mitterrand <cunctator@apartia.ch>
2000-09-19 15:28 ` Stuart Foster <stuartafoster@home.com>
2000-09-19 17:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-09-19 20:06   ` Louis-David Mitterrand <cunctator@apartia.ch>
2000-09-19 18:17 ` John McKown <joarmc@swbell.net>
2000-09-20 06:44   ` Louis-David Mitterrand <cunctator@apartia.ch>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox