Re: Group by and aggregates

On Thu, 2004-11-04 at 16:54, Michael L. Hostbaek wrote:
...
> some_id    partno    status        cmup    qty
> 1    test1    stock        10.00    15
> 2    test2    incoming    12.00    10
> 3    test1    incoming    15.00    60
> 4    test1    incoming    14.00    11
...
> 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 ?

junk=# select partno, status, (select max(cmup) from my_table as b where
b.partno = a.partno) as cmup, sum(qty) from my_table as a group by
partno, status, (select max(cmup) from my_table as b where b.partno =
a.partno);  partno |  status  | cmup  | sum
--------+----------+-------+-----test1  | incoming | 15.00 |  71test1  | stock    | 15.00 |  15test2  | incoming |
12.00|  10
 
(3 rows)

Oliver Elphick