TSQL Summary by account, what is the cleanest way to approach this?

Question

I am trying to total by account, the expenses & income per account. There are multiples of both the income & the expenses per account. I am struggling with this as am still learning SQL and thought that someone else likely has already addressed this? I sure would appreciate the help!

I know that this SQL server code is not correct but it at least gives a bit clearer picture of what I am attempting to do.

IF(SELECT(OBJECT_ID('TEMPDB..#Total'))) IS NOT NULL DROP TABLE #Total

declare @Expenses decimal(13,2), @income decimal(13,2)

set @expenses = sum(EXP_CHILD_CARE_AMOUNT) + sum(EXP_FOOD_AMOUNT) + sum(EXP_LIFE_INSURANCE_AMOUNT) + sum(EXP_TRANSPORTATION_AMOUNT) + sum(EXP_TUITION_AMOUNT) + sum(EXP_USER_2_AMOUNT) + sum(EXP_USER_3_AMOUNT) + sum(EXP_UTILITIES_AMOUNT)

set @income = (sum(NET_PAY_AMOUNT) + sum(OTHER_INCOME_AMOUNT)

SELECT F.LOAN_NUMBER, @Income, @Expenses

INTO #Total FROM OPENQUERY(SvrLink, '

SELECT F.Account, @Income, @Expenses

FROM finances F inner join account a on(a.Account = f.Account) where a.balance > 0

FETCH ONLY WITH UR ')

Answer 1

...ok, assuming that some fields are grouped into the same table (if not you'll just have to write the joins), you just need 1 query. (I wish all languages were as concise...) @AccountId is your desired account id.

SELECT l.LOAN_NUMBER, l.AccountId,
    (SELECT sum(EXP_CHILD_CARE_AMOUNT) + sum(EXP_FOOD_AMOUNT) + 
      sum(EXP_LIFE_INSURANCE_AMOUNT) + sum(EXP_TRANSPORTATION_AMOUNT) + 
      sum(EXP_TUITION_AMOUNT) + sum(EXP_USER_2_AMOUNT) + 
      sum(EXP_USER_3_AMOUNT) + sum(EXP_UTILITIES_AMOUNT) 
      as ExpenseTotal FROM Expenses_Guessing_The_Table_Name
      WHERE AccountId = @AccountId) as ExpenseTotal,
    (SELECT sum(NET_PAY_AMOUNT) + sum(OTHER_INCOME_AMOUNT) as IncomeTotal
      FROM Income_Guessing_The_Table_Name
      WHERE AccountId = @AccountId) as IncomeTotal
    FROM Loans l 
    WHERE  l.AccountId = @AccountId AND l.Balance > 0