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
------AB12CD34EF34GH12JK56
(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
whereclosed = 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 |  15CD34 |   5EF34 |   0GH12 |   4JK56 |   0
(5 Zeilen)


Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)              Tel. NL Heynitz:  035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    ===