Re: A Simple SQL Statement
From: Rob Goodrich <goodricr_at_postoffice.co.uk>
Date: 1997/10/27
Message-ID: <01bce2c3$16ad7da0$79f85790_at_goodrir>#1/1
Date: 1997/10/27
Message-ID: <01bce2c3$16ad7da0$79f85790_at_goodrir>#1/1
Your SQL is counting every row in the outer joined table for every matching record in the main table. The easiest way I know of doing what you propose is to perform a grouped select on each table separately; and surround their union with another grouped select. i.e...
SELECT key_item, SUM(sum_qty) FROM (
SELECT sd_item key_item, SUM(NVL(sd_qty,0)) sum_qty
FROM sales_details GROUP BY sd_item
UNION
SELECT pd_item key_item, SUM(NVL(pd_qty,0)) sum_qty
FROM purchase_details GROUP BY pd_item
) GROUP BY key_item
Hope this helps ! Received on Mon Oct 27 1997 - 00:00:00 CET