Thread: select & group by
I've got a problem selecting some specific data from my table. Imagine the following rows: part | mfg | qty | price | eta --------------------------------------- TEST1 ABC 10 100 (No ETA, as item is in stock) TEST1 ABC 12 120 04/04 TEST2 CBA 17 10 05/05 TEST2 CBA 10 20 (No ETA, as item is in stock) I'd like my selection to produce the following result: part | mfg | qty | qty incoming | highest price | eta ------------------------------------------------------------- TEST1 ABC 10 12 120 04/04 TEST2 CBA 10 17 20 05/05 Any clues on how to do this ? I kow the group by part, mfg, max(price) - but I do not know how to deal with the splitting up qty and stock qty and incoming qty. Thanks. /mich
Michael L. Hostbaek wrote: > I've got a problem selecting some specific data from my table. Imagine > the following rows: > > part | mfg | qty | price | eta > --------------------------------------- > TEST1 ABC 10 100 (No ETA, as item is in stock) > TEST1 ABC 12 120 04/04 > TEST2 CBA 17 10 05/05 > TEST2 CBA 10 20 (No ETA, as item is in stock) > > > I'd like my selection to produce the following result: > > part | mfg | qty | qty incoming | highest price | eta > ------------------------------------------------------------- > TEST1 ABC 10 12 120 04/04 > TEST2 CBA 10 17 20 05/05 > > Any clues on how to do this ? I kow the group by part, mfg, max(price) - > but I do not know how to deal with the splitting up qty and stock qty > and incoming qty. How about something like: SELECT aa.part, aa.mfg, aa.qty, bb.qty AS qty_incoming, CASE WHEN aa.price > bb.price THEN aa.price ELSE bb.price ENDAS highest_price, aa.eta FROM ( SELECT part,mfg,qty,price FROM mytable WHERE eta IS NOT NULL ) aa, ( SELECT part,mfg,qty,price FROM mytable WHEREeta IS NULL ) bb WHERE aa.part = bb.part AND aa.mfg=bb.mfg ; This is assuming you only have one row with "eta" set for each (part,mfg). If not, you'll have to identify which row you want. -- Richard Huxton Archonet Ltd
On Mon, 2005-04-04 at 12:16 +0200, Michael L. Hostbaek wrote: > I've got a problem selecting some specific data from my table. Imagine > the following rows: > > part | mfg | qty | price | eta > --------------------------------------- > TEST1 ABC 10 100 (No ETA, as item is in stock) > TEST1 ABC 12 120 04/04 > TEST2 CBA 17 10 05/05 > TEST2 CBA 10 20 (No ETA, as item is in stock) > > I'd like my selection to produce the following result: > > part | mfg | qty | qty incoming | highest price | eta > ------------------------------------------------------------- > TEST1 ABC 10 12 120 04/04 > TEST2 CBA 10 17 20 05/05 > > Any clues on how to do this ? I kow the group by part, mfg, max(price) - > but I do not know how to deal with the splitting up qty and stock qty > and incoming qty. use CASE. for example, something like: select part,mfg, sum(CASE WHEN eta is NULL then qty ELSE 0 END) as qty, sum(CASE WHEN eta is NULL then 0 ELSE qtyEND) as "qty incoming", max(price) as "highest price", min(eta) as eta group by part,mfg; gnari
On Mon, 2005-04-04 at 10:47 +0000, Ragnar Hafstað wrote: > On Mon, 2005-04-04 at 12:16 +0200, Michael L. Hostbaek wrote: > > [problem] > [slightly broken solution] I forgot a FROM clause, and you might want to add a ORDER BY clause, if that is important: select part,mfg, sum(CASE WHEN eta is NULL then qty ELSE 0 END) as qty, sum(CASE WHEN eta is NULL then 0 ELSE qtyEND) as "qty incoming", max(price) as "highest price", min(eta) as eta from thetable group by part,mfg order by part,mfg; gnari
Ragnar Hafstað (gnari) writes: > On Mon, 2005-04-04 at 10:47 +0000, Ragnar Hafstað wrote: > > On Mon, 2005-04-04 at 12:16 +0200, Michael L. Hostbaek wrote: > > > [problem] > > [slightly broken solution] > > I forgot a FROM clause, and you might want to add a > ORDER BY clause, if that is important: > > select part,mfg, > sum(CASE WHEN eta is NULL then qty ELSE 0 END) as qty, > sum(CASE WHEN eta is NULL then 0 ELSE qty END) as "qty incoming", > max(price) as "highest price", > min(eta) as eta > from thetable > group by part,mfg > order by part,mfg; Thanks, this works brilliantlty ! /mich