pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect & group by
5+ messages / 3 participants
[nested] [flat]
* select & group by
@ 2005-04-04 10:16 Michael L. Hostbaek <mich@the-lab.org>
2005-04-04 10:39 ` Re: select & group by Richard Huxton <dev@archonet.com>
2005-04-04 10:47 ` Re: select & group by Ragnar Hafstað <gnari@simnet.is>
0 siblings, 2 replies; 5+ messages in thread
From: Michael L. Hostbaek @ 2005-04-04 10:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select & group by
2005-04-04 10:16 select & group by Michael L. Hostbaek <mich@the-lab.org>
@ 2005-04-04 10:39 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2005-04-04 10:39 UTC (permalink / raw)
To: Michael L. Hostbaek <mich@the-lab.org>; +Cc: 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 END AS
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 WHERE eta 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select & group by
2005-04-04 10:16 select & group by Michael L. Hostbaek <mich@the-lab.org>
@ 2005-04-04 10:47 ` Ragnar Hafstað <gnari@simnet.is>
2005-04-04 10:59 ` Re: select & group by Ragnar Hafstað <gnari@simnet.is>
1 sibling, 1 reply; 5+ messages in thread
From: Ragnar Hafstað @ 2005-04-04 10:47 UTC (permalink / raw)
To: Michael L. Hostbaek <mich@the-lab.org>; +Cc: pgsql-sql
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 qty END) as "qty incoming",
max(price) as "highest price",
min(eta) as eta
group by part,mfg;
gnari
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select & group by
2005-04-04 10:16 select & group by Michael L. Hostbaek <mich@the-lab.org>
2005-04-04 10:47 ` Re: select & group by Ragnar Hafstað <gnari@simnet.is>
@ 2005-04-04 10:59 ` Ragnar Hafstað <gnari@simnet.is>
2005-04-04 12:23 ` Re: select & group by Michael L. Hostbaek <mich@the-lab.org>
0 siblings, 1 reply; 5+ messages in thread
From: Ragnar Hafstað @ 2005-04-04 10:59 UTC (permalink / raw)
To: Michael L. Hostbaek <mich@the-lab.org>; +Cc: pgsql-sql
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;
gnari
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: select & group by
2005-04-04 10:16 select & group by Michael L. Hostbaek <mich@the-lab.org>
2005-04-04 10:47 ` Re: select & group by Ragnar Hafstað <gnari@simnet.is>
2005-04-04 10:59 ` Re: select & group by Ragnar Hafstað <gnari@simnet.is>
@ 2005-04-04 12:23 ` Michael L. Hostbaek <mich@the-lab.org>
0 siblings, 0 replies; 5+ messages in thread
From: Michael L. Hostbaek @ 2005-04-04 12:23 UTC (permalink / raw)
To: Ragnar Hafstað <gnari@simnet.is>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-04-04 12:23 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-04 10:16 select & group by Michael L. Hostbaek <mich@the-lab.org>
2005-04-04 10:39 ` Richard Huxton <dev@archonet.com>
2005-04-04 10:47 ` Ragnar Hafstað <gnari@simnet.is>
2005-04-04 10:59 ` Ragnar Hafstað <gnari@simnet.is>
2005-04-04 12:23 ` Michael L. Hostbaek <mich@the-lab.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox