CODEWITHSUNDEEP
sqlpostgresqlspring-data-jparelational-databaserdbms
Vishal Gajera
Vishal Gajera

Reputation: 4207

How to achieve mathematical expression in postgres SQL query?

I have a kind of requirement to find out "Main Balance" from a PostgreSQL database table.

Here, The Logic is pretty much easy likewise,

Main Balance = Credit + Refund - Debit

I have a database table likewise,

Table Name :- Account

Columns :- id, source_account_number, destination_account_number, transaction_type, amount, creation_date_time

Here, transaction_type could have following set of possible values, CREDIT, REFUND, DEBIT.

Now, I would like to know for example what's my A/c balance then logic should be likewise,

Main Balance = sum of all CREDIT entries + sum of all REFUND entries - sum of all DEBIT entries.

So Basically, I am trying to achieve this through a single query.

For example(I know it's wrong but still let me paint),

select sum(amount) from Account where destination_account_number = 'XYZ' and transaction_type in ('CREDIT', 'REFUND');

But It'll return only deposited amount itself, what about DEBIT.

Obviously we can recover those as well but it's promoting for multiple query.

Can Anyone help me to do this through easiest, optimised way to achieve this functionality ?

Any help will really appreciable to all those who will show their interest !!

UPDATE - Here, transaction_type could have following set of possible values, CREDIT, REFUND, DEBIT, OTHERS, PENDING, EXPIRED. Now, Please consider Main Balance logic :- CREDIT + REFUND - DEBIT where PENDING, EXPIRED Money will be skipped in a main-balance calculation.

Upvotes: 0

Views: 400

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

This can be done with conditional aggregation.

select sum(case when transaction_type in ('CREDIT', 'REFUND') then amount else -amount end) 
from Account 
where destination_account_number = 'XYZ'

Upvotes: 1

Related Questions