Convert a running total oracle sql query to a final total

Question

I have the following query that works and gets me the total that I need.

However, i need to change it so that it just displays the final total instead of every transaction leading up to the total. I have tried converting it by group by rollup, group by group sets, using max transaction number, but my total never matches and I don't fully understand the SUM decode part of select statement. I believe i need to move part of that to the where statement to do what i need.

Here is the query:

SELECT
    SUM(DECODE(tbbdetc_type_ind,'C', (-1) * a.tbraccd_amount,'P',a.tbraccd_amount) ) OVER(
        PARTITION BY a.tbraccd_pidm
        ORDER BY
            a.tbraccd_tran_number
    ) "Running total",
    tbbdetc.tbbdetc_detail_code,
    tbbdetc.tbbdetc_type_ind,
    a.*
FROM
    tbraccd a
    JOIN taismgr.tbbdetc ON a.tbraccd_detail_code = tbbdetc.tbbdetc_detail_code
WHERE
    a.tbraccd_pidm = '101010101'
order by tbraccd_tran_number desc;

Any help on how to convert this so i just get the grand total that matches the final running total would be appreciated. My end goal would be to get the grand total for a list of pidms(IDS) one total for each pidm in a row.

score 1 · Accepted Answer · answered Jan 14 '20 at 22:54

1

You would seem to want to remove the order by. I'll also do the favor of converting to standard SQL syntax:

sum(case when tbbdetc_type_ind = 'C' then - a.tbraccd_amount
         when tbbdetc_type_ind = 'P' then a.tbraccd_amount
    end) over (partition by a.tbraccd_pidm) as total_total

answered Jan 14 '20 at 22:54

Gordon Linoff

1,242,037
58
646
786

and this would go in the select statement to replace the current sum(decode) in the select statement? – moore1emu Jan 14 '20 at 23:00
thank you, i added it to the select statement and it worked. how do i do a group by so it does nto show the final total 150 (once for each transaction)? I know i can do a distinct, but i know group by is more efficient. – moore1emu Jan 14 '20 at 23:05
@moore1emu . . . If you want *only* the total, then just use `select sum(case . . . ) from . . .`. If that doesn't quite do what you want, then ask a new question, with sample data, desired results, and a clear explanation. – Gordon Linoff Jan 15 '20 at 01:22

score 0 · Answer 2 · answered Mar 03 '20 at 20:18

I ended up going with this to get exactly what i needed:

Put this in the Select Statement

SUM(CASE 
    WHEN tbbdetc_type_ind = 'C' THEN -A.tbraccd_amount 
    WHEN tbbdetc_type_ind = 'P' THEN A.tbraccd_amount 
    END)  TOTAL_TOTAL

and this after the Group by:

HAVING SUM(CASE 
           WHEN tbbdetc_type_ind = 'C' THEN -A.tbraccd_amount 
           WHEN tbbdetc_type_ind = 'P' THEN A.tbraccd_amount 
           END) > -200.00

That ended up giving me the one result per a person instead of showing the total per each transaction

Convert a running total oracle sql query to a final total

2 Answers2