Basically, I want to know if there is a way to run 2 nested select statements in a query and then take the value of those and run a calculation on them without having to re-query them (or duplicate the selects in the statement). Attached is a simplified example of what I'd like to do, but doesn't work (my real statement is very large which is why I'm looking for a better way to do this). Obviously, it fails on the line that adds the 2 values together because it can't use those even though I've named them. I get that, but I'm wondering if there's a good way around it without writing a custom stored procedure or function.
(Select Sum(INCOME_CASH_AMOUNT) From TRANSACTIONS Where TRANSACTIONS.ACCOUNT_NUMBER=t.ACCOUNT_NUMBER) As INCOME_AMOUNT,
(Select Sum(PRINCIPAL_CASH_AMOUNT) From TRANSACTIONS Where TRANSACTIONS.ACCOUNT_NUMBER=t.ACCOUNT_NUMBER) As PRINCIPAL_AMOUNT,
(Select INCOME_AMOUNT + PRINCIPAL_AMOUNT) As TOTAL_AMOUNT
From ACCOUNT a
Inner Join TRANSACTIONS t On t.ACCOUNT_NUMBER = a.ACCOUNT_NUMBER