SUM problem
- 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
-
Try SUM( DISTINCT rmstranamt) instead of SUM(rmstranamt)
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
thanks, I tried that already, doesn't work...
-
Please check the sample below:
CREATE TABLE test2
(
testkey INT,
testAmt DECIMAL(7,2)
)
GO
INSERT INTO test2 (testkey, testAmt) VALUES (10, 3265.12)
INSERT INTO test2 (testkey, testAmt) VALUES (10, 3265.12)
INSERT INTO test2 (testkey, testAmt) VALUES (10, 3000.50)
INSERT INTO test2 (testkey, testAmt) VALUES (11, 365.12)
INSERT INTO test2 (testkey, testAmt) VALUES (11, 325.12)
INSERT INTO test2 (testkey, testAmt) VALUES (11, 300.50)
SELECT testKey, SUM(DISTINCT testAMt) FROM test2
GROUP BY testKey
testKey
----------- ----------------------------------------
10 6265.62
11 990.74
Isn't this what you want?
You are probably describing just the part of the problem because according to the code above it gets you what you want. (Unless I am just completly missing the point
)---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
yes, that's what I want...let me try DISTINCT one more time...
- 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
now, some sample data as requested: -
more sample data:
74973997364913 98.68 6005.62 5906.94 6189.30
0001089080 801.11 3054.49 2253.38 3855.60
UDJ63500028 89.00 1.00 88.00 90.00
5458000420010635 200.00 18695.68 18495.68 18095.68
4264293997809656 120.00 17888.46 17768.46 18498.29
4313027999739839 2285.00 3163.84 878.84 578.84
5490990997747967 256.62 28805.74 28549.12 30247.36
74993997319337 508.21 3969.35 3461.14 5747.94
5401639000464379 -5800.00 11980.66 6180.66 6180.66
74981995937812 800.00 6279.45 5479.45 5979.45
4313088999875932 60.00 9244.98 9184.98 13889.06
4264293999946639 390.93 9901.95 9511.02 15554.51
5329041999981688 -2000.00 9220.23 7220.23 3120.23
74973999825507 4848.18 4010.91 837.27 1920.91
4264296999951798 -209.70 18873.97 18664.27 17400.77
5490993999677935 12.50 1498.04 1485.54 1486.34
5490995999656942 -170.00 16983.26 16813.26 25768.38
4313024999953478 1520.22 6375.03 4854.81 8130.25
AZN82000233 81.00 1.00 80.00 82.00
000380373-01 0.00 1541.30 1541.30 3031.01
4403859158845135 1000.00 5288.14 4288.14 5288.14
4403859158845135 -1000.00 5288.14 4288.14 5288.14
5490995999307579 4182.50 7153.58 2971.08 2988.58
YME39600006 129.97 1.00 128.97 130.97
4313032999778035 -70.00 4969.20 4899.20 7781.85
5490995999588277 210.00 15953.12 15743.12 21203.13
RNF13900049 234.00 1.00 233.00 235.00
4264294999321724 -400.00 7856.09 7456.09 5156.09
NLD90000017 35.00 1.00 34.00 36.00
4313033999206647 251.40 5851.82 5600.42 8768.17
4264294999615331 -350.00 6965.95 6615.95 6965.95
4264294999615331 350.00 6965.95 6615.95 6965.95
4800128999799995 150.00 10052.33 9902.33 8817.33
BBR84900114 744.15 1.00 743.15 745.15
4264296999759902 85.00 6480.82 6395.82 7169.86
PUY16100002 11.50 228.71 217.21 240.21
5488975018469315 -261.85 2387.59 2125.74 716.49
NZC06000021 55.00 1.00 54.00 56.00
IYC78000047 60.00 1.00 59.00 61.00
THY15700010 129.00 1.00 128.00 130.00
5329017999698548 125.00 5975.81 5850.81 6307.49
5490350999143816 85.00 4793.80 4708.80 5060.83
5490995999888677 1514.03 9023.54 7509.51 10622.57
NLU50100015 60.00 1.00 59.00 61.00
RHU47700007 21.00 1.00 20.00 22.00
ANU00800232 74.00 1.00 73.00 75.00
74973997831127 -81.00 2005.55 1924.55 2005.55
74973997831127 81.00 2005.55 1924.55 2005.55
5490353999480477 2312.48 6163.66 3851.18 8961.14
4264294999242938 -400.00 6268.08 5868.08 3713.08
4226610353446863 585.00 4691.65 4106.65 3734.65
4313024997796739 85.00 3494.08 3409.08 3618.62
MYW66300052 48.00 1.00 47.00 49.00
MIN16600009 49.00 1.00 48.00 50.00
FDU95102349 35.00 1.00 34.00 36.00
4313027999854786 85.00 3447.78 3362.78 4082.63
5329050999412347 360.00 16388.09 16028.09 16633.90
5490994997595657 135.00 16911.96 16776.96 17553.88
5490994999879059 -35.00 4855.70 4820.70 7768.06
5490994999879059 35.00 4855.70 4820.70 7768.06
-
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
-
I don't really know what the final product of you query should be. You should post table definitions and sample data (CREATE statement and all INSERT statements) so we can run some tests. Your code is fairly large and trying to figure out what is wrong with it without sample data is kind of hard...
Try to break up the statement into smaller pieces and see if they work properly. I think you have some logical bug that is causing the problems...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
>>Try to break up the statement into smaller pieces and see if they work properly.
Agreed. I also think a derived table would really help with clarity, considering the number of times various SUM() expressions are re-used in expressions.
A derived table that does the aggregation, then a SELECT out of the derived table to handle all the CASE expressions.
Also consider the use of views. For example, the derived value of CurrentBalance - presumably this isn't the only place where the concept of "CurrentBalance" might be needed, so encapsulate that complexity in a view.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply