Query not producing correct results and Group By Error

This is what I need in my results:
Current Balance and Account Number should be retrieved where
Current Balance = Sum of rmstranamt - (sum of rmstranamt where rmstrancde is 10)

The Query below gives me a couple of problems

1) It's not producing the correct Current Balance

2) I keep getting these errors with Groupy by:

Column 'rf10.rmstranamt10' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT rm.rmsacctnum AS [Rms Acct Num],

(sum(rf.rmstranamt) - rf10.rmstranamt10) AS [Current Balance]

FROM RMASTER rm

INNER JOIN

(

SELECT RMSFILENUM, SUM(rmstranamt) AS rmstranamt10

FROM RFINANL

WHERE RMSTRANCDE = '10'

GROUP BY RMSFILENUM

) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(

SELECT RMSFILENUM, SUM(rmstranamt) AS rmstranamt

FROM RFINANL

GROUP BY RMSFILENUM

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

GROUP BY rm.rmsacctnum

The error message is correct as you would have to enclose the rf10.rmstranamnt10 inside a sum() to allow for the aggregation. I always recheck the SQL with an english translation of what I am trying to do. Your descriptive phrase shows the secondary sum, but the T-SQL does not have it.

I hope this helps,

|||

I did as you said and added the sum. I am still getting a Current Balance of 0.00 for an account # I checked when I know for a fact there are a total of 2 positive rmstranamt records in RFINANL with a RMSTRANCDE of 10 so I should have the sume of those in my results for that account number but it's 0.00

This query shows why

SELECT rm.rmsacctnum AS [Rms Acct Num],

sum(rf.rmstranamt) as Sum_rstranamt,

sum(rf10.rmstranamt10) as Sum_rmstranamt_10,

(sum(rf.rmstranamt) - sum(rf10.rmstranamt10)) AS [Current Balance]

FROM RMASTER rm

INNER JOIN

(

SELECT RMSFILENUM, SUM(rmstranamt) AS rmstranamt10

FROM RFINANL

WHERE RMSTRANCDE = '10'

GROUP BY RMSFILENUM

) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN

(

SELECT RMSFILENUM, SUM(rmstranamt) AS rmstranamt

FROM RFINANL

GROUP BY RMSFILENUM

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

AND rm.rmsacctnum = '4264287999172303'

GROUP BY rm.rmsacctnum

results:

4264287999172303 28789.50 28789.50 0.00

Sum_rstranamt should not be 28789.50 because there are 2 records for that account:

SELECT rm.rmsacctnum AS [Rms Acct Num],

rf.rmstranamt as [Rms rmstranamt],

rf.rmsbalance AS [Rms Balance]

FROM RMASTER rm

INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM

where rm.rmsacctnum = '4264287999172303'

4264287999172303 2878.95 0.00
4264287999172303 25910.55 28789.50

So Sum_rstranamt should be 2878.95 + 25910.55

|||

Here's a second look at what I've sruggled with:

I think we're almost there:
Let's work with account # '4264287999172303'
Query #1 - shows that there are 2 records in the RFINANL table for that account
SELECT rm.rmsacctnum AS [Rms Acct Num],
rf.rmstranamt as [Rms rmstranamt],
rf.rmsbalance AS [Rms Balance]
FROM RMASTER rm
INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM
where rm.rmsacctnum = '4264287999172303'
RESULTS:
4264287999172303 2878.95 0.00
4264287999172303 25910.55 28789.50

Query #2 - shows that there are 2 records in the RFINANL table for that account where RMSTRANCDE = '10'
--
select rm.rmsacctnum AS [Rms Acct Num], rf.rmstranamt
FROM RMASTER rm
INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM
where rf.RMSTRANCDE = '10'
and rm.rmsacctnum = '4264287999172303'
RESULTS:
4264287999172303 2878.95
4264287999172303 25910.55

Now this is what boggles my mind. If we were to take Query #1 and add a sum in it like below, you would get a result of 2878.95 + 25910.55 but I don't.
SELECT rm.rmsacctnum AS [Rms Acct Num],
sum(rf.rmstranamt) as [Rms rmstranamt], < added sum hoping to get 2878.95 + 25910.55
rf.rmsbalance AS [Rms Balance]
FROM RMASTER rm
INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM
where rm.rmsacctnum = '4264287999172303'
GROUP BY rm.rmsacctnum, rf.rmsbalance < but now I'm required to group by any values that do not contain an aggregate function
RESULTS:
4264287999172303 2878.95 0.00
4264287999172303 25910.55 28789.50
Take this a step further, now I add a sum to rf.rmsbalance since at this point I don't know what else do do. I then figure I should get 2878.95 + 25910.55 and 0.00 + 28.789.50 but again, I don't, I end up with both of them havin the same value. I have no idea why:
SELECT rm.rmsacctnum AS [Rms Acct Num],
sum(rf.rmstranamt) as [Rms rmstranamt],
Sum(rf.rmsbalance) AS [Rms Balance] <- Added sum to rf.rmsbalance
FROM RMASTER rm
INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM
where rm.rmsacctnum = '4264287999172303'
GROUP BY rm.rmsacctnum <- took out rf.rmsbalance because it's a sum now in the select
RESULTS:
4264287999172303 28789.50 28789.50

So at this point I'm still not getting this which I am ultimately wanting for each and every account if you we were to figure out the correct syntax:
(2878.95 + 25910.55) - (28789.50) as [Current Balance],
....