Re: Group by and aggregates

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>