I have four columns as follows:

How do I calculate Total Credit Amount and Total Credit Amount?

I tried the following which is a mess I think:

SELECT USER_ID , SUM(CR_total) as Total_Credit
FROM
(
        SELECT  USER_ID
             , Credit_Account
             , AMOUNT as CR_total
        FROM table  
        GROUP BY USER_ID
)CR

LEFT JOIN 
(SELECT USER_ID , SUM(DR_total) as Total_Debit
FROM
(
        SELECT USER_ID
             , Debit_Account
             , AMOUNT as DR_total
        FROM table
        GROUP BY DR.USER_ID
)DR
ON DR.USER_ID = CR.USER_ID
group by USER_ID
ORDER BY USER_ID

Expected Results:

And since there might be duplication of accounts in both columns(Credit_Account and Debit_Account), the required debit/credit amounts can be aggregated against each account.

CodePudding user response：

You can use a CROSS APPLY( VALUES...) to reshape your data into Account, Credit_Amount, and Debit_Amount. Standard grouping and aggregation operations can then be performed.

SELECT
    C.Account,
    SUM(C.Credit_Amount) AS Total_Credit_Amount,
    SUM(C.Debit_Amount) AS Total_Debit_Amount
FROM Data D
CROSS APPLY (
    VALUES
        (D.Credit_Account, D.AMOUNT, 0),
        (D.Debit_Account, 0, D.AMOUNT)
) C(Account, Credit_Amount, Debit_Amount)
GROUP BY C.Account

See this db<>fiddle.

The zero amounts may also be replaced will NULLs if you prefer.

If accounts are user-specific, you can add D.User_ID to the group by and select list.