Convert a running total oracle sql query to a final total

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.

Answer

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

Advertisement

Answer