I have a table that I want to aggregate by two different columns, prop1
and prop2
.
id | prop1 | prop2 | val
--------------------------
1 | A | B | 10
2 | A | A | 15
3 | B | B | 20
4 | B | A | 30
The desired output is:
prop_name | prop_val | sum_val
--------------------------------
prop1 | A | 25
prop1 | B | 50
prop2 | A | 45
prop2 | B | 30
I know I can do this using a union (see below), but is there a better way using the analytic functions?
with
test_data as (
select 1 as id, 'A' as prop1, 'B' as prop2, 10 as val from dual union all
select 2 as id, 'A' as prop1, 'A' as prop2, 15 as val from dual union all
select 3 as id, 'B' as prop1, 'B' as prop2, 20 as val from dual union all
select 4 as id, 'B' as prop1, 'A' as prop2, 30 as val from dual
)
select
'prop1' as prop_name
, prop1 as prop_val
, sum(val) as sum_val
from test_data
group by 'prop1', prop1
union all
select
'prop2' as prop_name
, prop2 as prop_val
, sum(val) as sum_val
from test_data
group by 'prop2', prop2;
I would do this with grouping sets:
select prop1, prop2, sum(val)
from test_data
group by grouping sets ((prop1), (prop2))
Here is your example.
Getting your exact output requires a bit more work.
select (case when prop1 is null then 'prop2' else 'prop1' end) as prop_name,
coalesce(prop1, prop2) as prop,
sum(value)
from test_data
group by grouping sets ((prop1), (prop2));
This assumes that the first two columns do not contain NULL
values. The better way to express the logic is using GROUPING_ID
or GROUP_ID()
, but I think the logic is easier to follow with COALESCE()
.