Re: A Simple SQL Statement

From: terryg8 <trg_at_ibm.net>
Date: 1997/10/24
Message-ID: <34515A4F.6490_at_ibm.net>#1/1

allsoft_at_hd1.vsnl.net.in wrote:
>
> Can someone help me solve this simple problem ? There are two tables,
> and
> I
> need a summary of the items. For SUM(SD_QTY) I get the right figure,
> but
> for
> SUM(PD_QTY) I get the figure multiplied by 3.
>
> ------------------- Table : PURCHASE_DETAILS
> -----------------------
> 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
> ----------------------- Thank you in advance -------------------
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

Hi,

To identify the problem, first look at the query results without the sum and group by.
The join alone would produce rows like

 SD_ITEM SD_QTY PD_ITEM PD_QTY

       3      100          3         2
       3        1          3         2
       3        3          3         2
.

.
.
So with that intermediate result set you can see how summing sd_qty would give you 104 and summing pd_qty would give you 6. The summing and order by operate on and after the RESULTS of the join.

You want the sum of sales_details and purchase_details independant of one another right?

I suggest creating a simple view to hold the distinct sd_items and then using embedded tables to perform the query.

i.e.
create view v_items as select distinct sd_item from sales_details;

then

select v_items.sd_item, s_details.sum_sd_qty, p_details.sum_pd_qty from v_items,

      (select sum(sd_qty) "SUM_SD_QTY"
       from sales_details,v_items
       where sales_details.sd_item = v_items.sd_item ) s_details,
      (select sum(pd_qty) "SUM_PD_QTY"
       from purchase_details,v_items
       where pd_item = v_items.sd_item ) p_details

Ideally, your v_items view could be replaced by a table of valid items.

This works in Oracle 7.2.2, it may not in earlier versions.

Cheers,
Terry Received on Fri Oct 24 1997 - 00:00:00 CEST