Search code examples
sqloracle-databasesum

Oracle SQL summing columns and displaying the totals at the bottom of the list

I'm using this to create total for each cost value:

SELECT
    ITEM_NO
    COST_1,    
    COST_2,
    COST_3,
    COST_4,   
    SUM(COST_1)  over (order by loan_no rows unbounded preceding ) COST_1_TOTAL,
    SUM(COST_2)  over (order by loan_no rows unbounded preceding ) COST_2_TOTAL,
    SUM(COST_3)  over (order by loan_no rows unbounded preceding ) COST_3_TOTAL,
    SUM(COST_4)  over (order by loan_no rows unbounded preceding ) COST_4_TOTAL
FROM data

it creates this:

OUTPUT

I'd like to have it look like this:

enter image description here

Is this possible. I'm not sure where to begin...

As always, any advise would be appreciated.

Solution

  • Use GROUP BY and ROLLUP:

    SELECT ITEM_NO,
       SUM(COST_1) AS cost_1,
       SUM(COST_2) AS cost_2,
       SUM(COST_3) AS cost_3,
       SUM(COST_4) AS cost_4
FROM   data
GROUP BY ROLLUP(item_no)

    Which, for the sample data:

    CREATE TABLE data (item_no, cost_1, cost_2, cost_3, cost_4) AS
SELECT 1057, 8111.73, -8600, 1550, CAST(NULL AS NUMBER(8,2)) FROM DUAL UNION ALL
SELECT 1693, 7856.25, -8600, 1575, NULL FROM DUAL UNION ALL
SELECT 1701, 5206.20, -8600, 2000, NULL FROM DUAL UNION ALL
SELECT 1784, 6002.97, -8600, 1900, NULL FROM DUAL;

    Which outputs:

    ITEM_NO COST_1 COST_2 COST_3 COST_4
    1057 8111.73 -8600 1550 null
    1693 7856.25 -8600 1575 null
    1701 5206.2 -8600 2000 null
    1784 6002.97 -8600 1900 null
    null 27177.15 -34400 7025 null

    fiddle