SUM Problem

[dba112233]

The problem with my SUM below is that I don't want it to sum up like amounts. For example, there may be multiple rmstranamt showing but if in the list there are 2 that are alike, just sum up one of them along with the others. In other words, I need to somehow do a grouping of the rmstranamt so that I'm not summing up one unique number twice in my SUM.

Example output. Lets say my query finds 3 rmstranamt records for a particular account. Right now it's taking 3265.12 + 3265.12 + 3000.50 which is not right. I need just 3265.12 + 3000.50 and to ignore the other duplicate 3265.12:

10 3265.12
10 3265.12
10 3000.50

Part of my SQL Statement that is selecting this:

INNER JOIN
(
SELECT RMSFILENUM,
SUM(rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

 

[gmmastros]

Add another subquery where you select the distinct values and then sum that. Try this, it may work for you.

INNER JOIN 
(
SELECT RMSFILENUM, 
       SUM(rmstranamt) AS rmstranamt10
FROM   (
       Select Distinct RMSFILENUM, rmstranamt10 
       From   RFINANL 
       Where  RMSTRANCDE = '10
       ) As A
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dba112233]

SELECT RMSFILENUM,
SUM(DISTINCT rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM


doesn't work, it's still summing up like values:

OUTPUT IS:

4313030999894992 263.31 6530.24 6266.93 3528.43

Should be

4313030999894992 263.31 3265.12 6266.93 3528.43 <----- SHOULD NOT BE ADDING 3265.12 x 2 so this is correct

The 2 records that it's summing looks like this:

4313030999894992 85.00 3265.12 3180.12 3528.43 <----- do not add both 32.65, just take one and then add to 3000.12
4313030999894992 178.31 3265.12 3086.81 3528.43

 

[dba112233]

same for this:

SELECT RMSFILENUM,
SUM(rmstranamt) AS rmstranamt10
FROM (
Select Distinct RMSFILENUM, rmstranamt
From RFINANL
Where RMSTRANCDE = '10'
) As A
GROUP BY RMSFILENUM

I am still not getting just 3265.12

 

[dba112233]

Here's the whole query so you can see what's going on:

SELECT rm.rmsacctnum,
SUM(rf.rmstranamt) AS [Sum rmstranamt],
SUM(rf10.rmstranamt10) AS [Sum rmstranamt 10],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
WHEN SUM(rf.rmstranamt) = 0 AND SUM(rf10.rmstranamt10) = 0 Then
0.00
WHEN SUM(rf.rmstranamt) = 0 AND SUM(rf10.rmstranamt10) <> 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) <> 0 AND SUM(rf10.rmstranamt10) = 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],

cb.CurrentBalance

FROM RMASTER rm

INNER JOIN
(
SELECT RMSFILENUM,
SUM(DISTINCT rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

INNER JOIN
(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)
+(RMSASSCCST - RMSRCVDCST)
+(RMSACRDINT - RMSRCVDINT)
+(UDCCOSTS1 - UDCRECCS1)
+(UDCCOSTS2 - UDCRECCS2)
+(RMSCOST1 - RMSCOST1R)
+(RMSCOST2 - RMSCOST2R)
+(RMSCOST3 - RMSCOST3R)
+(RMSCOST4 - RMSCOST4R)
+(RMSCOST5 - RMSCOST5R)
+(RMSCOST6 - RMSCOST6R)
+(RMSCOST7 - RMSCOST7R)
+(RMSCOST8 - RMSCOST8R)
+(RMSCOST9 - RMSCOST9R)
+(RMSCOST10 - RMSCOST10R)
- RMSXCSRCVS
) as CurrentBalance
FROM RPRDBAL)
AS cb ON cb.RMSFILENUM = rm.RMSFILENUM
WHERE rf.rmstrancde IN ('10', '16','18','19','20','21','22','29','30','31','36','37','38','3A','3B','3C','3D','3E','3F','3M','3N','3O','3P','3Q','3R','3T',
'3U','3X','3Z','40','41','42','43','44','45','46','47','48','49','4A','4B','4D','4E','4H','4J','4X','4Z','50','51','52','53',
'55','56','57','58','5A','5B','5C','5P','5Q','5R','5X','5Z')
and rm.rmsacctnum = '4313030999894992'
GROUP BY rm.rmsacctnum, cb.CurrentBalance
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00