Re: A Simple SQL Statement
Date: 1997/10/21
Message-ID: <877450975.7238_at_dejanews.com>#1/1
In article <877416656.16852_at_dejanews.com>,
allsoft_at_hd1.vsnl.net.in wrote:
>
> -----------------------
> PD_ITEM PD_QTY
> 3 2
>
> ------------------- Table : SALES_DETAILS
> -------------------------
> SD_ITEM SD_QTY
> 3 100
> 3 1
> 3 3
> 4 200
> SB 1
> ---------------------- S Q L --------------------------------
> SELECT SD_ITEM, SUM(SD_QTY), SUM(PD_QTY)
> FROM SALES_DETAILS, PURCHASE_DETAILS
> WHERE SD_ITEM = PD_ITEM (+)
> GROUP BY SD_ITEM;
> ----------------------- Result --------------------------
> SD_ITEM Sum(SD_QTY) Sum(PD_QTY)
> 3 104 6 <-------- This should be
> 2
> 4 200
> SB 1
When you join your two tables, this is what you get
SD_ITEM SD_QTY PD_QTY 3 100 2 3 1 2 3 3 2
After Sum, PD_QTY became 6.
If your PD_ITEM is unique, try the following SQL:
SELECT SD_ITEM, SUM(SD_QTY), (SUM(PD_QTY)/COUNT(PD_QTY))
FROM SALES_DETAILS, PURCHASE_DETAILS
WHERE SD_ITEM = PD_ITEM (+)
GROUP BY SD_ITEM;
-------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to UsenetReceived on Tue Oct 21 1997 - 00:00:00 CEST