How to write this SQL query to find each account balance?

Question

I have a transaction table (shown in picture)

https://i.ibb.co/7pdYxxm/hhhhhh.jpg

There's the transaction type (debit/credit) I need a SQL query that calculates the balance of each account (sum of credits - sum of debts) So we group by account_id ... but how can we sum the credits alone and the debits alone?

I am on PostgreSQL! Thank you!

Answer 1

I assume the amount must be substracted when the type = 'C'.

    select account_id, sum((case when transaction_type = 'C' then -1 else 1 end) * transaction_amount)
    from trans
    group by account_id

base on the transaction type the amount is multiplied by 1 or -1.