Message-ID: <418A6831.9060306@onesystem.com>
Date: Thu, 04 Nov 2004 10:34:41 -0700
From: Edmund Bacon <ebacon@onesystem.com>
User-Agent: Mozilla Thunderbird 0.8 (X11/20040913)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Group by and aggregates
References: <20041104165430.GD94770@mich2.itxmarket.com>
In-Reply-To: <20041104165430.GD94770@mich2.itxmarket.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Michael L. Hostbaek wrote:
> List, 
> 
> I've got a table looking something like this:
> 
> my_table
> 	some_id int bla bla,
> 	partno varchar(100),
> 	status varchar(100),
> 	cmup numeric(14,2),
> 	qty int
> 
> My SQL select statement will then group together partno, status and
> aggregate sum(qty) and max(cmup). This is all good and nice.
> 
> My result will look something like this:
> 
> partno	status		cmup	qty
> test1	stock		10.00	15
> test1	incoming	15.00	71
> test2	incoming	12.00	10
> 
> Now, I need the first line to say "15.00" in the cmup field. That is,
> stock and incoming are obviously not being grouped, but since it's the
> same partno I'd like somehow to show the highest cmup. Is there some
> black SQL voodoo that'll achieve this ?
>

You *CAN* sort by aggregates
e.g.

select partno, status, sum(cmup) as cmup, sum(qty) as qty
    from my_table
    group by partno, status
    order by partno, sum(cmup) desc;

  partno |  status  | cmup | qty
--------+----------+------+-----
  test1  | incoming |   29 |  71
  test1  | stock    |   10 |  15
  test2  | incoming |   12 |  10


-- 
Edmund Bacon <ebacon@onesystem.com>