Re: brain F*rt question

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: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).