pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmaximum of sums
3+ messages / 3 participants
[nested] [flat]
* maximum of sums
@ 2002-02-19 05:54 Rob <r_e_l_a_x_e_d_r_o_b@optushome.com.au.remove.underscores@news.tht.net>
0 siblings, 2 replies; 3+ messages in thread
From: Rob @ 2002-02-19 05:54 UTC (permalink / raw)
To: pgsql-sql
Howdy all!
Let's say we have a product orders table like this:
SQL> select * from orders;
ORDER_NO PROD_NO QUANTITY
--------- --------- ---------
1 2 3
2 2 3
3 3 3
4 1 4
5 3 8
6 2 7
7 1 6
8 1 3
9 3 3
10 2 4
10 rows selected.
SQL>
I want to select the prod_no and sum (quantity) for the product with the max sum
(quantity).
I have this so far:
SQL> select
2 max (sumamt) as maximum
3 from
4 (select
5 sum (orders.quantity) as sumamt
6 from
7 orders
8 group by
9 orders.prod_no);
MAXIMUM
---------
17
SQL>
But how can I get the matching prod_id?
Any help would be much appreciated!
Rob
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: maximum of sums
@ 2002-02-19 09:58 ThomasR <rehlich@wtal.de>
parent: Rob <r_e_l_a_x_e_d_r_o_b@optushome.com.au.remove.underscores@news.tht.net>
1 sibling, 0 replies; 3+ messages in thread
From: ThomasR @ 2002-02-19 09:58 UTC (permalink / raw)
To: pgsql-sql
Rob wrote:
> Howdy all!
>
[snap table]
>
> I have this so far:
> SQL> select
> 2 max (sumamt) as maximum
> 3 from
> 4 (select
> 5 sum (orders.quantity) as sumamt
> 6 from
> 7 orders
> 8 group by
> 9 orders.prod_no);
>
> MAXIMUM
> ---------
> 17
>
> SQL>
>
> But how can I get the matching prod_id?
perhaps with the limit-clause:
select prod_no, sum( quantity ) as sumq
from orders
group by prod_no
order by sumq desc
limit 1
But it fails, if you have two or more products with the same amount of
orders
Thomas
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: maximum of sums
@ 2002-02-21 21:55 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Rob <r_e_l_a_x_e_d_r_o_b@optushome.com.au.remove.underscores@news.tht.net>
1 sibling, 0 replies; 3+ messages in thread
From: Jean-Luc Lachance @ 2002-02-21 21:55 UTC (permalink / raw)
To: pgsql-sql
Rob,
How about
select prod_no, sum( quantity) from orders group by prod_no order by 2
desc limit 1;
JLL
Rob wrote:
>
> Howdy all!
>
> Let's say we have a product orders table like this:
>
> SQL> select * from orders;
>
> ORDER_NO PROD_NO QUANTITY
> --------- --------- ---------
> 1 2 3
> 2 2 3
> 3 3 3
> 4 1 4
> 5 3 8
> 6 2 7
> 7 1 6
> 8 1 3
> 9 3 3
> 10 2 4
>
> 10 rows selected.
>
> SQL>
>
> I want to select the prod_no and sum (quantity) for the product with the max sum
> (quantity).
>
> I have this so far:
> SQL> select
> 2 max (sumamt) as maximum
> 3 from
> 4 (select
> 5 sum (orders.quantity) as sumamt
> 6 from
> 7 orders
> 8 group by
> 9 orders.prod_no);
>
> MAXIMUM
> ---------
> 17
>
> SQL>
>
> But how can I get the matching prod_id?
>
> Any help would be much appreciated!
>
> Rob
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-02-21 21:55 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-19 05:54 maximum of sums Rob <r_e_l_a_x_e_d_r_o_b@optushome.com.au.remove.underscores@news.tht.net>
2002-02-19 09:58 ` ThomasR <rehlich@wtal.de>
2002-02-21 21:55 ` Jean-Luc Lachance <jllachan@nsd.ca>
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