Re: [despammed] sum query

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 ===