Обсуждение: 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