select max of sums [message #18957] |
Fri, 22 February 2002 16:24 |
Robert Mark Bram
Messages: 3 Registered: February 2002
|
Junior Member |
|
|
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
|
|
|
|
Correction [message #19007 is a reply to message #18957] |
Mon, 25 February 2002 16:13 |
Nanda Ramanathan
Messages: 1 Registered: February 2002
|
Junior Member |
|
|
I think this is what you wanted to be precise
QL> select X "prod_no", Y "Sum(quanity)", Z "Maximum"
2 from V1, V2
3 where Y = Z;
prod_no Sum(quanity) Maximum
-------- ------------ ---------
2 17 17
|
|
|