Re: A Simple SQL Statement
From: Emmanuel Greciet & Michelle Skamene <mskamene_at_ndirect.co.uk>
Date: 1997/10/26
Message-ID: <34530BB0.185_at_ndirect.co.uk>#1/1
SELECT SD_ITEM, SUM(SD_QTY), MAX(PD_QTY) FROM SALES_DETAILS, PURCHASE_DETAILS
WHERE SD_ITEM = PD_ITEM (+)
GROUP BY SD_ITEM, PD_ITEM;
Date: 1997/10/26
Message-ID: <34530BB0.185_at_ndirect.co.uk>#1/1
You need to group on your PD table as well. Try
SELECT SD_ITEM, SUM(SD_QTY), MAX(PD_QTY) FROM SALES_DETAILS, PURCHASE_DETAILS
WHERE SD_ITEM = PD_ITEM (+)
GROUP BY SD_ITEM, PD_ITEM;
If you use SUM(PD_QTY), then you will multiply your PD_QTY as many times as you have matching records in your Sales_Details table. So in the example you gave, since you have 3 matching records in SD, you will get 2 * 3 = 6...
By grouping on the Purchase_Details table, you will only retrieve the PD_QTY once for each matching record in SD. You have to use an aggregate function (MIN, MAX, SUM, etc..) when you group, hence the MAX(PD_QTY)... YOu could just as easily use MIN, since you are only retrieving one record.
Good luck! Received on Sun Oct 26 1997 - 00:00:00 CEST