brain F*rt question

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Wed, 19 Dec 2001 08:11:38 -0800
Message-ID: <F001.003E0108.20011219062531@fatcity.com>

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

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

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).

Received on Wed Dec 19 2001 - 10:11:38 CST