Wednesday, March 28, 2012

Query problem with subtraction

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) - (select rf.rmstranamt FROM RMASTER rm
INNER JOIN RFINANL rff ON rff.RMSFILENUM = rm.RMSFILENUM
where rff.RMSTRANCDE = '10') AS [Current Balance],
FROM RMASTER rm
INNER JOIN RFINANL rf ON rf.RMSFILENUM = rm.RMSFILENUM
GROUP BY rm.rmsacctnum

something wrong with how I'm trying to calculate the subtraction here. I'm not sure the syntaxt to include the divisor in this particular situation. I need to subtract from the rmstranamt where rmstrancde is 10 and there should only be one results for the divisor

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)

If you need to find Sum of rmstranamt - (sum of rmstranamt where

rmstrancde is 10), then you probably need SUM in your subquery.

If that doesn't help, you'll need to be more specific about what you

mean by "something wrong". Do you get an error message? Do

you get wrong answers? Does your computer explode?

Steve Kass

Drew University|||There were syntax problems throwing errors but I corrected them. Thanks for your reply though.

No comments:

Post a Comment