SQL Query Find sums from one column for two different columns-CodePudding

I have a Table with Debit Accounts, Credit Accounts and Amount of Transactions between them. I need to take every Account(Which might be debit sometimes and credit sometimes) and add two columns of received amount and sent amount. I tried a few things, but I only can show for example;

Accounts of debit and Sum of Sent money, same with credits. How can I gather all this parts together? I want to find distinct of accounts from both columns Because sometimes one account might be credit, sometimes debit, and also find how much money it sent or recieved enter image description here

WITH TRANSACTIONS (ID,Trans_ID_DT,Trans_ID_CR,Amount)
AS
(
  SELECT 2865991,5420422,5434125,279.72 UNION ALL
  SELECT 2865992,5415415,5434125,20.28  UNION ALL
  SELECT 2866486,5415438,5434502,279.72  
)

CodePudding user response：

SELECT T.Trans_ID_DT,SUM(T.Amount)AS SumDebet,0.00 AS SumCredit
FROM TRANSACTIONS AS T
GROUP BY T.Trans_ID_DT
UNION ALL
SELECT Trans_ID_CR,0.00 AS SumDebet,SUM(T.Amount)AS SumCredit
FROM TRANSACTIONS AS T
GROUP BY T.Trans_ID_CR

or you need something like this ?

CodePudding user response：

Unpivot the columns to rows and then add the credits and subtract the debits.

In RDBMS that support UNPIVOT (like Oracle):

SELECT account_id,
       SUM(sgn * amount) AS balance
FROM   transactions
UNPIVOT (account_id FOR sgn IN (trans_id_dr AS -1, trans_id_cr AS  1))
GROUP BY account_id;

or, using ANSI SQL:

SELECT account_id,
       SUM(amount) AS balance
FROM   (
  SELECT trans_id_dr AS account_id, -amount AS amount FROM transactions
UNION ALL
  SELECT trans_id_cr AS account_id,  amount AS amount FROM transactions
)
GROUP BY account_id;

Which, for the sample data:

CREATE TABLE transactions (id, trans_id_dr, trans_id_cr, amount) AS
SELECT 2865991, 5420422, 5434125, 279.72 FROM DUAL UNION ALL
SELECT 2865992, 5415415, 5434125,  20.28 FROM DUAL UNION ALL
SELECT 2866486, 5415438, 5434502, 279.72 FROM DUAL;

Both output:

ACCOUNT_ID BALANCE

5415438 -279.72

5434125 300

5420422 -279.72

5434502 279.72

5415415 -20.28

ACCOUNT_ID	BALANCE
5415438	-279.72
5434125	300
5420422	-279.72
5434502	279.72
5415415	-20.28

db<>fiddle here