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],
....
