Re: A Simple SQL Statement

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 Usenet