CODEWITHSUNDEEP
sqldatabasepostgresql
Otto
Otto

Reputation: 153

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

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!

Upvotes: 0

Views: 5020

Answers (4)

Sonika B R
Sonika B R

Reputation: 1

Select sum(Transaction_Amount), 
   Account_Id, 
   Transaction_Type
From Table_Name
Group By
   Account_Id,
   Transaction_Type

Upvotes: 0

Sunil Cyriac
Sunil Cyriac

Reputation: 523

This is an easy method to for you to achieve this:

select account_id, sum((case when transaction_type = 'C' then 1 else 0 end)*transaction_amount) as sum_of_credit,sum((case when transaction_type = 'D'then 1 else 0 end) * transaction_amount) as sum_of_debit from YourTableNameHere group by account_id;

Sample Data Sample Output for the query

Upvotes: 1

Conffusion
Conffusion

Reputation: 4475

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.

Upvotes: 0

xCloudx8
xCloudx8

Reputation: 721

Try This: 

 Select sum(Transaction_Amount), 
       Account_Id, 
       Transaction_Type

    From Table_Name
    Group By
       Account_Id,
       Transaction_Type

Upvotes: 0

Related Questions