Re: [despammed] [SQL] 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=== ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] sum query
[if replying, please do so to the list / newsgroup only, not to me] On 2004-12-03, "Keith Worthington" <[EMAIL PROTECTED]> wrote: > 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? First, notice that what you're asking for involves a row of output for each item in tbl_item regardless of whether it appears in the other tables at all. This is an indication that says "try an outer join here". So bearing that indication in mind, we work out what the other side of the outer join should look like. This would be a simple join on the other two tables to give the quantity in open orders. (Note that there are two ways to do the grouping/summation in this query; either on the result of the two-table join or on the final result.) You used the same table name twice in your example data, I'll assume that was an error and that the third table should have been called tbl_order_item. Here then is how to construct the query: Start by working out the quantities: select oi.id,sum(oi.quantity) from tbl_order_item oi join tbl_order o using (order_id) where o.closed=false group by id; id | sum --+- GH12 | 4 AB12 | 15 CD34 | 5 (3 rows) Now outer-join that against tbl_item: select id,quantity from (select oi.id,sum(oi.quantity) as quantity from tbl_order_item oi join tbl_order o using (order_id) where o.closed=false group by id) as oj right join tbl_item i using (id); id | quantity --+-- AB12 | 15 CD34 |5 EF34 | GH12 |4 JK56 | (5 rows) However this gives us NULL for the quantities not appearing on any open order. Since we want zero instead, we remove the nulls with COALESCE: select id,coalesce(quantity,0) as quantity from (select oi.id,sum(oi.quantity) as quantity from tbl_order_item oi join tbl_order o using (order_id) where o.closed=false group by id) as oj right join tbl_item i using (id); id | quantity --+-- AB12 | 15 CD34 |5 EF34 |0 GH12 |4 JK56 |0 (5 rows) And we have the desired result. Notice that I have not used ORDER BY; if you want results in a given order, add that yourself. I mentioned that the grouping could be done in two ways. Here is the other way: Start with the ungrouped quantity figures: select oi.id,oi.quantity from tbl_order_item oi join tbl_order o using (order_id) where o.closed=false; id | quantity --+-- AB12 | 10 CD34 |5 GH12 |4 AB12 |5 (4 rows) Outer-join against tbl_item: select id,quantity from (select oi.id,oi.quantity from tbl_order_item oi join tbl_order o using (order_id) where o.closed=false) as oj right join tbl_item i using (id); id | quantity --+-- AB12 | 10 AB12 |5 CD34 |5 EF34 | GH12 |4 JK56 | (6 rows) Unlike with the previous version, this time we can flatten out the inner select (should make no difference to performance but may be more readable): select id,quantity from tbl_order_item oi join tbl_order o on (o.order_id=oi.order_id and o.closed=false) right join tbl_item i using (id); id | quantity --+-- AB12 | 10 AB12 |5 CD34 |5 EF34 | GH12 |4 JK56 | (6 rows) And then group the values and handle nulls: select id,coalesce(sum(quantity),0) as quantity from tbl_order_item oi join tbl_order o on (o.order_id=oi.order_id and o.closed=false) right join tbl_item i using (id) group by id; id | quantity --+-- AB12 | 15 CD34 |5 EF34 |0 GH12 |4 JK56 |0 (5 rows) Notice I haven't used either IN or UNION. Using IN in place of a join is unwise (even though recent versions can sometimes plan it as though it were a join); using UNION in place of an outer join is _very_ unwise. (In fact UNION / INTERSECT / EXCEPT should normally be reserved for those cases where there is simply no alternative.) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] sum query
am Sat, dem 04.12.2004, um 12:38:11 - mailte Andrew - Supernews folgendes: Thank you for the very good explanation. > Notice I haven't used either IN or UNION. Using IN in place of a join is > unwise (even though recent versions can sometimes plan it as though it were > a join); using UNION in place of an outer join is _very_ unwise. (In fact > UNION / INTERSECT / EXCEPT should normally be reserved for those cases > where there is simply no alternative.) Okay. I'm learning ;-) Andreas -- Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau- fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] inserting values into types
CREATE TYPE qwerty_UDT AS (abc INT); CREATE TABLE t (col1 qwerty_UDT); INSERT INTO t (col1) VALUES (qwerty_UDT(123)); ERROR: function qwerty_udt(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. It seems as though you want to create your own user-defined types. That isn't easy in PostgreSQL. You have to create an external C struct and write a function to convert a PostgreSQL string to a C struct and return it to the database (called an input function), and create another function that takes an object from the database and returns a string to the database in order to print out the object (called an output function). The only thing PostgreSQL knows about the object is size of the object and the input and output functions. You also need to be the database administrator. Go here for more information: http://www.postgresql.org/docs/current/static/xtypes.html I had to create an object-oriented database for one of my classes, and although I wanted to use PostgreSQL, I didn't want to deal with so low level stuff. Oracle is much better for object-oriented features. Regards, Yasir ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
