Re: A Simple SQL Statement

In article <6303vi$iq8$1_at_analog.skynet.be>,   "REIP Jean-Claude" <jacari.consultant.noospaamm_at_skynet.be> wrote:
>
> Hello,
>
> With your join, you got something like
>
> SD_ITEM SD_QTY PD_QTY
> 3 100 2
> 3 1 2
> 3 3 2
> 4 200 2
> SB 1 2
>
> which is summarised and you found 6 PD of course !
>
> Perhaps can you create a view that summarise SD
>
> CREATE VIEW SUMSD AS
> SELECT sd_item, SUM(sd_qty) su_qty
> FROM sale_details
> GROUP BY sd_item;
>
> and then select
>
> SELECT sd_item, su_qty, pd_qty
> FROM sumsd, sales_details
> WHERE sd_item = pd_item;
>
> but, are you really sure that's what you want ? Perhaps creating another sum
> view for purchasing ?
>
> Hope this help
>
> --
> REIP Jean-Claude
> Enlevez ".remove.this" de l'adresse (anti-spam)
> jacari.consultant.remove.this_at_skynet.be
>
> allsoft_at_hd1.vsnl.net.in wrote in message <877416656.16852_at_dejanews.com>...
> >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

Instead of a view a table would help because views do not support bind variables. I am using it fine using a table, but what if someone else wants to access the same report at the same time - the table would already be created and he would have to hang on. Also creating tables is slow. There actually must be a simpler method.

Regards,

Feroz Bhote

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet