2

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.

2
  • Have you tried ROLLUP in db2? I think this is what you need. Very powerful for calculating Running Numbers Totals and Averages. May 4 '16 at 7:22
  • You could also try SUM(CS_WG_CST) OVER(PARTITION BY CS_YEAR, CS_MNTH, CS_WK), etc. as a way to "pivot" and sum on those fields. You would need one SUM() OVER() for each field you wish to sum on. May 4 '16 at 16:12
3

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)

2
  • 1
    why did you use the group by rollup? Unless I missed something (highly likely :-) the grouping sets should be sufficient.
    – Lennart
    May 6 '16 at 19:20
  • 1
    sorry - was a copy&paste problem (from my other test) I have edited it and removed it to avoid confusion. Thanks for pointing that out! May 7 '16 at 7:24
1

Have you tried GROUPING SETS? Something like:

SELECT  CS_YEAR AS YEAR,
        CS_MNTH AS MONTH,
        CS_WK AS WEEK_NO,
        COALESCE(COST_CARRIER, 'Total') AS COST_CARRIER,
        COALESCE(COST_CARRIER_CD, '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
  AND CS_MNTH = 1 AND
  AND CS_WK = 2 AND
  AND CS_MFC_CD = 'CBU'
GROUP BY GROUPING SETS((CS_YEAR, CS_MNTH, CS_WK)
                      ,(CS_YEAR, CS_MNTH, CS_WK, COST_CARRIER_CD, COST_CARRIER));

I used COALESCE to map NULL to 'Total' assuming that - say - COST_CARRIER is NOT NULL. If you want to distinguish between a "genuine" null, and a grouped null, there is a function GROUPING that can be used to determine this. Example:

select year, month, week
     , case grouping(cost_carrer) 
            when 0 then cost_carrer 
            when 1 then 'Total' 
       end 
...

EDIT: I see now that @MichaelTiefenbacher answered in a similar way, and that my only contribution is the use of COALESCE. Using his sample data:

select year, month, week, coalesce(cost_carrer, 'Total') as 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;

YEAR        MONTH       WEEK        COST_CARRER 5        6        7                                         
----------- ----------- ----------- ----------- -------- -------- --------
       2016           1           2 0              40000    60000  100000
       2016           1           2 1              60000    80000  140000
       2016           1           2 2             150000   210000  360000
       2016           1           2 Total         250000   350000  600000

Apparently I managed to add the data twice, which explains why my numbers are doubled compared to Michaels.

As pointed out by Michael you can also group by rollup and cube. Group by rollup and group by cube are "syntactic sugar" for grouping sets (which in turn are "syntactic sugar" for union's of group by). Group by rollup (a,b,c) is the same as:

group by grouping sets ((a,b,c),(a,b),(a),()). 

Group by cube is a grouping set with the power set of the grouping attributes. Hence, group by cube (a,b,c) is the same as:

group by grouping sets ((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),()). 

Among the more esoteric usages of group by cube is to use it as a generator (not very efficient though)

select 1 from ( values 1 ) t(n)
group by cube (n,n,n,n,n)

will produce 2^5 (5 is the number of n's in the cube) = 32 rows. To produce a large number of rows it might be tempting to nest the cubes, but that is not allowed:

SQL0481N  The GROUP BY clause contains "CUBE" nested within "CUBE".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.