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 | 71
test1 | stock | 15.00 | 15
test2 | incoming | 12.00 | 10
(3 rows)

Oliver Elphick