How to sum from multiple columns and segregate into separate column if result is positive and negative

Question

I am using postgresql and need to write a query to sum values from separate columns of two different tables and then segregate into separate columns if positive or negative.

For Example,

Below is the source table

Below is the resultant table which need to be created also used while populating it

I have written below query to aggregate sum and able to populate TOT_CREDIT and TOT_DEBIT column. Is there any optimized query to achieve that ?

select  t.account_id,
        t.transaction_date,
        SUM(t.transaction_amt) filter (where t.transaction_amt >= 0) as tot_debit,
        SUM(t.transaction_amt) filter (where t.transaction_amt < 0) as tot_credit,
        case
        when
        (
            SUM(t.transaction_amt) + 
            SUM(COALESCE(b.credit_balance,0)) +
            SUM(COALESCE(b.debit_balance,0))
        ) < 0
        then
        (
            SUM(t.transaction_amt) + 
            SUM(COALESCE(b.credit_balance,0)) +
            SUM(COALESCE(b.debit_balance,0))
        )
        end as credit_balance,
        case
        when
        (
            SUM(t.transaction_amt) + 
            SUM(COALESCE(b.credit_balance,0)) +
            SUM(COALESCE(b.debit_balance,0))
        ) > 0
        then
        (
            SUM(t.transaction_amt) + 
            SUM(COALESCE(b.credit_balance,0)) +
            SUM(COALESCE(b.debit_balance,0))
        )
        end as debit_balance,
from 
    transaction t   

LEFT OUTER JOIN balance b ON (t.account_id = b.account_id 
                                        and t.transaction_date = b.transaction_date 
                                        and b.transaction_date=t.transaction_date- INTERVAL '1 DAYS')
group by
    t.account_id,
    t.transaction_date

Please provide some pointer.

EDIT 1: This query is not working in expected manner.

VSK · Answer 1 · 2020-09-02T12:32:22.657

One way is to break your logic into smal queries and join them in the end!

select tw.account_id, tw.t_date,tw.t_c,th.T_D,fo.C_B,fi.d_B from 
(select account_id, Transaction_date as t_date, sum(Transaction_AMT) as t_C from TransactionTABLE 
where Transaction_AMT<0 group by  account_id, Transaction_date ) as tw inner join
(select account_id, Transaction_date as t_date, sum(Transaction_AMT) as t_d from TransactionTABLE 
where Transaction_AMT>0 group by  account_id, Transaction_date ) as th on tw.account_id=th.account_id and tw.t_date=th.t_date inner join
(select account_id, Transaction_date as t_date, sum(Transaction_AMT) as C_B from TransactionTABLE 
where sum(Transaction_AMT)<0 group by  account_id, Transaction_date ) as fo on th.account_id=fo.account_id and th.t_date=fo.t_date inner join
(select account_id, Transaction_date as t_date, sum(Transaction_AMT) as d_B from TransactionTABLE 
where sum(Transaction_AMT)>0 group by  account_id, Transaction_date ) as fi on fi.account_id=fo.account_id and fi.t_date=fo.t_date;

Or else

You could try something as follows which calculates the running count of d_B over the Transaction_date and account_id

select  account_id,
        transaction_date,
        SUM(transaction_amt) filter (where transaction_amt >= 0) as tot_debit,
        SUM(transaction_amt) filter (where transaction_amt < 0) as tot_credit,
sum(transaction_amt) over (partition by account_id where sum(transaction_amt)<0) as credit_balance,
sum(transaction_amt) over (partition by account_id where sum(transaction_amt)>=0) as debit_balance
from TransactionTABLE group by account_id, Transaction_date order by 1,2;

I have editted my question and provided working query , just wanted to know which way is better — user2800089, Sep 02 '20 at 07:32
Maybe, you can check it by yourself by testing the complexity and execution time of both the queries and then you have your answer!! — VSK, Sep 02 '20 at 07:39
My query is not working in expected way. Is there any way by which I can have less joins as shown in your query to achieve performance benefit — user2800089, Sep 02 '20 at 10:12
You could use over partition method to calculate such running counts in your scenario. I'm not sure whether, I have used them properly, why don't you give it a try? More on this can be found at [link](https://stackoverflow.com/questions/29250503/cannot-use-group-by-and-overpartition-by-in-the-same-query) — VSK, Sep 02 '20 at 12:35

How to sum from multiple columns and segregate into separate column if result is positive and negative

1 Answers1