db2groupingdb2-luwsubtotal

DB2/LUW - How to get group totals and sub-totals without a null label for the grand total?


I've read the answers for group totals with sub-totals. But none of them seem to address the issue of having a NULL label for the grand total. I tried both ROLLUP() and GROUPING SETS but they both do much the same thing. For example:

Select GROUP_KEY_TYPE, Count(*) as group_count
  from LIBRAT.GL_GROUPS
 Group by Grouping Sets((GROUP_KEY_TYPE),())
 Order by GROUP_KEY_TYPE;

That yields the following result set:

GROUP_KEY_TYPE  GROUP_COUNT
A               35
B               351
D               13
I               18
W               17
<null>          434

However, I want some kind of "TOTAL" label instead of the NULL value. I can do this with a UNION of two queries.

Select GROUP_KEY_TYPE, Count(*) as group_count
  from LIBRAT.GL_GROUPS
 Group by GROUP_KEY_TYPE
UNION ALL
Select '{TOTAL}', COUNT(*) as group_count
  from LIBRAT.GL_GROUPS
 Order by GROUP_KEY_TYPE;

But, is there a way to do much the same thing without processing the data twice? Thanks.


Solution

  • You may use the GROUPING aggregate function for that along with the conditional decoding of the GROUP_KEY_TYPE column value as in the example below.

    Select 
        DECODE(GROUPING(GROUP_KEY_TYPE), 0, GROUP_KEY_TYPE, 'TOTAL') GROUP_KEY_TYPE
      , GROUPING(GROUP_KEY_TYPE) AS GROUP_KEY_TYPE_G  -- just for demo
      , Count(*) as group_count
    from (values 'A', 'A', 'B', NULL) MYTAB(GROUP_KEY_TYPE)
    Group by 
      rollup(MYTAB.GROUP_KEY_TYPE)
      --Grouping Sets((MYTAB.GROUP_KEY_TYPE),())
    Order by GROUPING(MYTAB.GROUP_KEY_TYPE), MYTAB.GROUP_KEY_TYPE
    

    The result is:

    GROUP_KEY_TYPE GROUP_KEY_TYPE_G GROUP_COUNT
    A 0 2
    B 0 1
    null 0 1
    TOTAL 1 4

    fiddle