Re: multiple table join and sumation

On Fri, Dec 03, 2004 at 06:16:32PM -0500, Keith Worthington wrote:

> id | committed | on_order
> ------+-----------+---------
> A | 15 | 10
> B | 9 | 5
> C | 0 | 0
> D | 0 | 0
> E | 0 | 2

The following gives the above results when used against your example
data. I look at it and think it ought to be simpler, so maybe somebody
will post an improvement.

SELECT i.id,
COALESCE(s.sum, 0) AS committed,
COALESCE(p.sum, 0) AS on_order
FROM tbl_item AS i
LEFT OUTER JOIN (
SELECT id, SUM(quantity)
FROM tbl_sales_item JOIN tbl_sales USING ("order")
WHERE closed IS FALSE
GROUP BY id
) AS s USING (id)
LEFT OUTER JOIN (
SELECT id, SUM(quantity)
FROM tbl_purchase_item JOIN tbl_purchase USING ("order")
WHERE closed IS FALSE
GROUP BY id
) AS p USING (id)
ORDER BY id;

BTW, when providing example data, it's helpful if you post SQL
statements that create and populate the example tables. That way
people can paste those statements into their own database and verify
their solutions against your data.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/