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


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

Original text of this message