Re: A Simple SQL Query

So, to fix it, divide SUM(PD_QTY) by count(SD_QTY) Martin Hebrank <heller@devel.nacs.net> wrote in article <632rqc$tb2$1@tracy.nacs.net>... > In comp.databases.adabas allsoft@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; > : ----------------------- > > Yup. notice you have 3 rows with an SD_ITEM of 3. So it adds 2 in > for each time the join matches, which is 3 times. Check it by > changing the columns to simply a '*' so you can see the full output. > > ** Martin > > -- > "...endowed by their Creator with certain unalienable Rights...Life, Liberty > and the pursuit of Happiness...That whenever ANY Form of Government becomes > destructive of these ends, it is the Right of the People to alter or to > abolish it, and to institute new Government...most likely to effect their > Safety and Happiness. " > -- Declaration of Independence and a reminder to Congress >