From: Andreas Kretschmer <akretschmer@despammed.com>
To: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: [despammed] sum query
Date: Sat, 4 Dec 2004 10:41:25 +0100
Message-ID: <20041204094125.GA14802@Pinguin.wug-glas.de> (raw)
In-Reply-To: <20041203213340.M8095@narrowpathinc.com>
References: <20041203213340.M8095@narrowpathinc.com>
am 03.12.2004, um 16:33:40 -0500 mailte Keith Worthington folgendes:
> Hi All,
>
> I am trying to join three tables and sum the quantities.
>
> The first table contains all of the possible items. The second table contains
> orders and the third table contains the items on each order.
>
> For all items found in tbl_item I need the total quantity on open orders. If
> an item is not on any open order the quantity should be zero.
>
> Can someone please explain the best way to do this?
>
> tbl_item
> id | ...
> ------+...
> AB12 | ...
> CD34 | ...
> EF34 | ...
> GH12 | ...
> JK56 | ...
>
> tbl_order
> order | closed |...
> ------+--------+...
> 1 | false |...
> 2 | true |...
> 3 | true |...
> 4 | false |...
> 5 | false |...
>
> tbl_item
> order | id | quantity
> ------+-------+---------
> 1 | AB12 | 10
> 1 | CD34 | 5
> 2 | CD34 | 3
> 3 | EF34 | 2
> 3 | GH12 | 20
> 4 | GH12 | 4
> 5 | AB12 | 5
>
> id | quantity
> ------+---------
> AB12 | 15
> CD34 | 5
> EF34 | 0
> GH12 | 4
> JK56 | 0
>
test_db=# select * from tbl1;
id
------
AB12
CD34
EF34
GH12
JK56
(5 Zeilen)
test_db=# select * from tbl2;
order_id | closed
----------+--------
1 | f
2 | t
3 | t
4 | f
5 | f
(5 Zeilen)
test_db=# select * from tbl3;
order_id | id | quantity
----------+------+----------
1 | AB12 | 10
1 | CD34 | 5
2 | CD34 | 3
3 | EF34 | 2
3 | GH12 | 20
4 | GH12 | 4
5 | AB12 | 5
(7 Zeilen)
Now i have this sql:
select id, sum(summe) from (select id, sum(quantity) as summe from tbl3 where order_id in (select order_id from tbl2 where closed = false) group by id union select id,0 from tbl1 group by id) as x group by x.id order by x.id;
and this result:
id | sum
------+-----
AB12 | 15
CD34 | 5
EF34 | 0
GH12 | 4
JK56 | 0
(5 Zeilen)
Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Tel. NL Heynitz: 035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: akretschmer@despammed.com
Subject: Re: [despammed] sum query
In-Reply-To: <20041204094125.GA14802@Pinguin.wug-glas.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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