Re: select & group by - Mailing list pgsql-sql

From Richard Huxton
Subject Re: select & group by
Date
Msg-id 4251197C.1060904@archonet.com
Whole thread Raw
In response to select & group by  ("Michael L. Hostbaek" <mich@the-lab.org>)
List pgsql-sql
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


pgsql-sql by date:

Previous
From: "Michael L. Hostbaek"
Date:
Subject: select & group by
Next
From: Ragnar Hafstað
Date:
Subject: Re: select & group by