Db2 – How to get the Sum of all column values in the last row of a resultset without using UNION

db2

I need to get the sum of all column values of a result set in the last row.
Here is my SQL query.

SELECT  CS_YEAR AS YEAR,
        CS_MNTH AS MONTH,
        CS_WK AS WEEK_NO,
        'Total' AS COST_CARRIER,
        'Total' AS COST_CARRIER_CD,
        SUM(CS_WG_CST) AS WAGE_COST,
        SUM(CS_PART_CST) AS MATERIAL_COST,
        SUM(CS_DH_CST) AS DH_SUBLET_COST,
        SUM(CS_TOTAL_CST) AS TOTAL_COST

FROM    ASPECT.WR_CD_CC_RPT_SUMM
WHERE
        CS_CNTRY_CD = '81930' AND
        CS_YEAR = 2016 AND
        CS_MNTH = 1 AND
        CS_WK = 2 AND
        CS_MFC_CD = 'CBU'
GROUP BY CS_YEAR, CS_MNTH, CS_WK

UNION ALL

SELECT  CS_YEAR AS YEAR,
        CS_MNTH AS MONTH,
        CS_WK AS WEEK_NO,
        CS_CC_KIND AS COST_CARRIER,
        CS_CC_CD AS COST_CARRIER_CD,
        CS_WG_CST AS WAGE_COST,
        CS_PART_CST AS MATERIAL_COST,
        CS_DH_CST AS DH_SUBLET_COST,
        CS_TOTAL_CST AS TOTAL_COST

FROM    ASPECT.WR_CD_CC_RPT_SUMM
WHERE
        CS_CNTRY_CD = '81930' AND
        CS_YEAR = 2016 AND
        CS_MNTH = 1 AND
        CS_WK = 2 AND
        CS_MFC_CD = 'CBU'

I am getting the result like this-

enter image description here

is there any alternate way to get the total of all the cost on the basis of year, month and week no without using UNION.

Best Answer

It really depends on your main goal - if it should look 100% like your result in the question above then I am sorry the answer is no. If you want to get a result with the sum etc. yes there are ways round the union but you might not see the "Total" text.

Here is one solution idea (with less data and columns as I could not extract it from your question).

create table test (year integer, month integer, week integer, cost_carrer char(1),
                    wage_cost decimal(10,2), material_cost decimal (10,2), total_cost decimal(12,2));

insert into test values (2016, 1, 2, '0', 20000.00, 30000.00, 50000.00),
                        (2016, 1, 2, '1', 30000.00, 40000.00, 70000.00),
                        (2016, 1, 2, '2', 25000.00, 35000.00, 60000.00),
                        (2016, 1, 2, '2', 25000.00, 35000.00, 60000.00),
                        (2016, 1, 2, '2', 25000.00, 35000.00, 60000.00);

select year, month, week, cost_carrer, sum(wage_cost), sum(material_cost), sum(total_cost)
 from test
 group by grouping sets ((year, month, week,  cost_carrer), (year, month, week))
 order by year, month, week, cost_carrer

This will return:

YEAR MONTH WEEK COST_CARRER WAGE_COST MATERIAL_COST TOTAL_COST
 ---- ----- ---- ----------- --------- ------------- ----------
 2016     1    2 0            20000.00      30000.00   50000.00
 2016     1    2 1            30000.00      40000.00   70000.00
 2016     1    2 2            75000.00     105000.00  180000.00
 2016     1    2 NULL        125000.00     175000.00  300000.00

And I think this is the "idea" behind your question and example.

Other - even more powerful group by variantions are "group by rollup" and "group by cube" which is described here in the documention (with examples)

Related Question