I've got solution for the aggregations I'm looking for, however I believe there is a shorter way to achieve same output with a single query.
The engine is RedShift.
select
a
,null as b
,null as c
,sum(fact) as f
from foo
group by grouping sets(
a
, ())
union all
select
a
,b
,null as c
,sum(fact) as f
from foo
group by grouping sets(
(a,b)
, (a))
union all
select
a
,b
,c
,sum(fact) as f
from foo
group by grouping sets(
(a,b,c)
, (a,b)
)
;
See the SQL sample. A shorter query to achieve the same outcome.