I am trying to determine what "level" of a ROLLUP report each given row belongs to. When initial data contains no NULLs, then it is possible just count nulls in every row (amount of null cells is the same for each given level/layer of grouping), although it also does not appear to me as a beautiful solution.
In case when initial data contains NULLs this workaround works no more: in the example query results table you see that <null> comes from real nulls, and (null) is a standard NULL placeholder for a grouped cell.
I expect to find something similar to "virtual columns as in hierarchical queries (like LEVEL, PATH, etc.).
Is there a natural way in PostgreSQL v12 to determine a level of a row in report built with ROLLUP (and in general with GROUPING SETS)?
Example of ROLLUP query result:
select a, b, sum(d.c)
from(
select null as a, 2 as b, 1 as c
union all
select null as a, 3 as b, 1 as c
union all
select 'a1' as a, 4 as b, 1 as c
union all
select 'a1' as a, 5 as b, 1 as c
union all
select 'a2' as a, 6 as b, 1 as c
) d GROUP by ROLLUP (d.a, d.b)
| a | b | sum |
--------------------------
| (null) | (null) | 5 |
| a1 | 5 | 1 |
| <null> | 2 | 1 |
| a1 | 4 | 1 |
| a2 | 6 | 1 |
| <null> | 3 | 1 |
| a2 | (null) | 1 |
| <null> | (null) | 2 |
| a1 | (null) | 2 |
--------------------------
Use Grouping, kind of
select case when grouping(a) = 1 then 'Total' else cast(a as varchar(20)) end a,
case when grouping(b) = 1 then 'Total' else cast(b as varchar(20)) end b, sum(d.c)
from(
select null as a, 2 as b, 1 as c
union all
select null as a, 3 as b, 1 as c
union all
select 'a1' as a, 4 as b, 1 as c
union all
select 'a1' as a, 5 as b, 1 as c
union all
select 'a2' as a, 6 as b, 1 as c
) d GROUP by ROLLUP (d.a, d.b)
order by grouping(a), a, grouping(b), b