X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C14C03A5BD3 for ; Sat, 4 Dec 2004 09:41:48 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 03134-02 for ; Sat, 4 Dec 2004 09:41:35 +0000 (GMT) Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.184]) by svr1.postgresql.org (Postfix) with ESMTP id 081463A5106 for ; Sat, 4 Dec 2004 09:41:34 +0000 (GMT) Received: from [212.227.126.161] (helo=mrelayng.kundenserver.de) by moutng.kundenserver.de with esmtp (Exim 3.35 #1) id 1CaWPz-0006be-00 for pgsql-sql@postgresql.org; Sat, 04 Dec 2004 10:41:31 +0100 Received: from [194.25.154.138] (helo=pinguin.schollglas.com) by mrelayng.kundenserver.de with asmtp (Exim 3.35 #1) id 1CaWPy-0001Pa-00 for pgsql-sql@postgresql.org; Sat, 04 Dec 2004 10:41:31 +0100 Received: from kretschmer by pinguin.schollglas.com with local (Exim 3.33 #6) id 1CaWPt-0003sq-00 for pgsql-sql@postgresql.org; Sat, 04 Dec 2004 10:41:25 +0100 Date: Sat, 4 Dec 2004 10:41:25 +0100 From: Andreas Kretschmer To: PostgreSQL SQL Subject: Re: [despammed] sum query Message-ID: <20041204094125.GA14802@Pinguin.wug-glas.de> References: <20041203213340.M8095@narrowpathinc.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20041203213340.M8095@narrowpathinc.com> User-Agent: Mutt/1.4i X-DEST: 208 X-MSMail-Priority: High X-MSMail-Virus: X5O!P%@AP[4PZX54(P^)7CC)7}-STANDARD-ANTIVIRUS-TEST-FILE!+H* X-Message-Flag: My name is root. Just root. And I am licensed to kill -9 X-PrivatMail: kretschmer@kaufbach.delug.de X-OS: Linux - weil ich es mir Wert bin! X-Info: registrierter Linux-User 97922 http://counter.li.org X-LUG: http://lug-dd.schlittermann.de X-Handy: 0160 / 8256486 (privat) X-Fax: 035204 / 40505 (privat) X-Provags-ID: kundenserver.de abuse@kundenserver.de auth:51ea9561fc3f6d9539a2acd743e4748c X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.759 tagged_above=0 required=5 tests=DNS_FROM_RFC_WHOIS, MISSING_MIMEOLE, X_MSMAIL_PRIORITY_HIGH X-Spam-Level: X-Archive-Number: 200412/33 X-Sequence-Number: 19653 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 ===