SQL to calculate total balance amount of each owner - debit/credit

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
give a positive ratinggive a negative rating
16 Dec 2022 at 01:31 PM

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;
give a positive ratinggive a negative rating
17 Dec 2022 at 11:18 AM
Share on FacebookShare on TwitterShare on LinkedInSend email
2024 AnswerTabsTermsContact us