| From: | "Keith Worthington" <keithw(at)narrowpathinc(dot)com> |
|---|---|
| To: | "PostgreSQL SQL" <pgsql-sql(at)postgresql(dot)org> |
| Subject: | sum query |
| Date: | 2004-12-03 21:33:40 |
| Message-ID: | 20041203213340.M8095@narrowpathinc.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-sql |
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
Kind Regards,
Keith
______________________________________________
99main Internet Services http://www.99main.com
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andreas Kretschmer | 2004-12-04 09:41:25 | Re: [despammed] sum query |
| Previous Message | Johan Henselmans | 2004-12-03 20:02:21 | Re: grouping a many to many relation set |