Sum variables when condition is met in sql

I have a table mes_transaction where I have variable dc – it’s either DEBIT or CREDIT. I am writing a sql procedure for another table(if I can say so). In the other table mes_aggregated_trx I have trx_amt – transaction amount variable. Basically what I need to do is to write a procedure that would sum trx_amt in mes_aggregated_trx based on dc from mes_transaction.

It would need to look like this(pseudo code):

IF dc = ‘CREDIT’ THEN ADDTOSUM(trx_amt) ELSE SUBFROMSUM(trx_amt)

I can’t figure it out how to implement the IF clause in the sql. Or maybe I should do something like summing CREDIT trx_amt, then summing DEBIT trx_amt, and then subtracting the sums to get desired result? But the problem is that the procedure looks like this:

$BODY$
BEGIN
    INSERT INTO mes_aggregated_trx(pos_id
                                  ,mes_account_id
                                  ,merchant_code
                                  ,trx_count
                                  ,fee_amount
                                  ,trx_amount
                                  ,date)
    SELECT pos_id
          ,mes_account_id
          ,merchant_code as location_id
          ,count(1) as count
          ,sum(comm_amt) as fee_amount
          ,sum(trx_amt) as trx_amount
          ,aggregation_date 
    FROM mes_transaction
    WHERE post_date =aggregation_date
    AND bic = aggregation_bic
    AND rec_type='TRX'
    AND (status='OK' OR status='OVR')
    GROUP BY pos_id, merchant_code, mes_account_id
    ON CONFLICT (date, merchant_code, pos_id, mes_account_id)
    DO UPDATE
       SET trx_count = excluded.trx_count
          ,trx_amount = excluded.trx_amount
          ,fee_amount = excluded.fee_amount;

    -- Conflicts shouldn't happen since before insert new trx for date everything
    -- for that date is cleaned, but to be 100% sure not to duplicate date, newest
    -- date is stored.
RETURN 1;  
END;
$BODY$

So I would need to somehow redo the whole structure of the procedure, and I don’t really know that much to do that. So any help, tips or pieces of advice would be great! Thanks

Answer

IF dc = 'CREDIT' THEN ADDTOSUM(trx_amt) ELSE SUBFROMSUM(trx_amt)

would be in SQL something like:

SUM(CASE WHEN dc='CREDIT' THEN trx_amt ELSE -(trx_amt) END)

Advertisement

Answer