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.
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 |