pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rob <"r_e_l_a_x_e_d_r_o_b@optushome.com.au.remove.underscores"@news.tht.net>
To: pgsql-sql@postgresql.org
Subject: maximum of sums
Date: Tue, 19 Feb 2002 16:54:08 +1100
Message-ID: <3c71e972$0$16639$afc38c87@news.optusnet.com.au> (raw)

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





view thread (3+ messages)  latest in thread

Message-ID: <3c71e972$0$16639$afc38c87@news.optusnet.com.au>
Permalink:  ../3c71e972$0$16639$afc38c87@news.optusnet.com.au/
Also on:    postgresql.org/message-id/3c71e972$0$16639$afc38c87@news.optusnet.com.au

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: "r_e_l_a_x_e_d_r_o_b@optushome.com.au.remove.underscores"@news.tht.net
  Subject: Re: maximum of sums
  In-Reply-To: <3c71e972$0$16639$afc38c87@news.optusnet.com.au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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