Re: Group by and aggregates

If I understand well, you want the highest cmup for each partno, that is
max(cmup) grouped by partno (only).

You can achieve this with a subselect, and then you join the results
whith the query you already have:

SELECT T.partno, T.status, TMP.max_cmup_for_partno, max(T.cmup) AS
max_cmup, sum(T.qty) AS sum_qty
FROM my_table T, (SELECT partno, max(cmup) AS max_cmup_for_partno FROM
my_table GROUP BY partno) AS TMP
WHERE tmp.partno=T.partno
GROUP BY T.partno, TMP.max_cmup_for_partno, T.status

Hope it helped.

On Thu, 2004-11-04 at 13:54, 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
>
> Here a small sample of contents in my table:
>
> 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 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 ?
>
> TIA,