Michael L. Hostbaek wrote:
>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 ?
I think you need
Franco Bruno Borghesi (franco) writes:
> If I understand well, you want the highest cmup for each partno, that is
> max(cmup) grouped by partno (only).
>
> 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,
On Thu, 2004-11-04 at 16:54, Michael L. Hostbaek wrote:
...
> some_id partno status cmupqty
> 1 test1 stock 10.00 15
> 2 test2 incoming12.00 10
> 3 test1 incoming15.00 60
> 4 test1 incoming14.00 11
...
> My result
On Thu, Nov 04, 2004 at 05:54:30PM +0100, Michael L. Hostbaek wrote:
> some_id partno status cmupqty
> 1 test1 stock 10.00 15
> 2 test2 incoming12.00 10
> 3 test1 incoming15.00 60
> 4 test1 incoming14.00 11
>
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
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
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 cmupqty
1 test1
