How should the SQL Select look like, if I need to get the total account balance for each customer, from debit and credit transactions ?
Table looks like:
Transaction ID, Account Owner ID, Transaction Type, Amount
1, 5, credit, 100
2, 5, debit, 40
To calculate total balance of each bank account owner from debit and credit transactions, you need to convert debit amounts to negative numbers (using SQL CASE), then SUM the transaction amounts and group relevant transactions (using GROUP BY).
The SQL should be:
SELECT account_owner_id, SUM(CASE WHEN transaction_type = 'credit' THEN amount ELSE amount*-1 END) as balance FROM transactions GROUP BY account_owner_id;