maximum of sums

From: "Rob" <"r_e_l_a_x_e_d_r_o_b(at)optushome(dot)com(dot)au(dot)remove(dot)underscores"(at)news(dot)tht(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: maximum of sums
Date: 2002-02-19 05:54:08
Message-ID: 3c71e972$0$16639$afc38c87@news.optusnet.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Mark kirkwood 2002-02-19 08:37:45 Transient Disk Usage Higher In 7.2 ?
Previous Message Christopher Kings-Lynne 2002-02-19 01:26:19 Re: obtaining row number of select