Ron Rogers wrote: > > List, > I am having a big brain F*rt on a simple join query. I would like some input please. > Two tables with common fields "retnbr and saledate" and other fields. I would like to > select the saledate, sum of selected fields from table 1 and selected fields from >table 2 > for a particuler retnbr and group the results by saledate. > I keep getting the summed values increased by the number of occurances in the two >tables. > listing of the correct output from table1 (glciwsr): > SALDATE INSETTLE INRET INCASH INCOMM INBONUS > ---------- ---------- ---------- - --------- >---------- ---------- > 01-06-2001 900 0 -555 -45 >-11.1 > 01-13-2001 1800 0 -885 -90 -17.7 > 01-20-2001 300 -218 -724 -4.1 -14.48 > 01-27-2001 600 0 -767 -30 - >15.34 > > listing of the correct output from table2 (glcowsr); > SALDATE OLSALES OLCASH OLCOMM OLBONUS > ---------- ---------- ---------- ---------- >---------- > 01-06-2001 7470.5 -694 -504.78 -13.88 > 01-13-2001 8106 -1651 -547.88 -33.02 > 01-20-2001 7215 -865 -488.29 -17.3 > 01-27-2001 6438.5 -1085 -428.58 -21.7 > > What I would like is the all of the columns to appear on one list with only 1 >occurance of the > SALDATE. > > listing of the query I used that gives the wrong results > > select a.saledate saldate, > (sum(a.settlementamt) - sum(a.returnamt)) insettle, > SUM(a.returnamt) inret, > SUM(a.cashamt) incash, > SUM(a.SALESCOMMAMT)*-1 incomm, > SUM(a.CASHBONUSAMT)*-1 inbonus, > sum(b.salesamt) olsales, > sum(b.cashamt)*-1 olcash, > sum(b.salescommamt)*-1 olcomm, > sum(b.cashbonusamt)*-1 olbonus > from glciwsr a , glcowsr b > where a.retnbr = &retlook and > b.retnbr = a.retnbr and > a.saledate between '&startdt' and '&endate' > and b.saledate = a.saledate > group by a.saledate; > > output from incorrect query: > SALDATE INSETTLE INRET INCASH INCOMM INBONUS OLSALES >OLCASH OLCOMM > ---------- ---------- ---------- --------- - >--------- ---------- ----------- ---------- >---------- > 01-06-2001 4500 0 -2775 -225 >-55.5 201703.5 -18738 -13629.06 > 01-13-2001 9000 0 -4425 -450 >-88.5 243180 -49530 -16436.4 > 01-20-2001 1500 -1090 -3620 -20.5 >-72.4 202020 -24220 -13672.12 > 01-27-2001 3000 0 -3835 -150 >-76.7 160962.5 -27125 -10714.5 > > As you can see the sum's are increased 5 fold. > Any help in clearing the Brain F*rt would be appreciated. > Ron > > ROR m���m
Ron, Looks to me like the classical percentage computation problem. Sums are wrong because applied to the result of the join. Compute your sums in an in-line view in the FROM clause. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
