I have this dataset that I want to group by user_id and sum the values by category
id | user_id | category | value |
---|---|---|---|
0 | 1 | 1 | 10 |
1 | 1 | 1 | 20 |
2 | 1 | 2 | 10 |
3 | 1 | 2 | 30 |
4 | 1 | 3 | 10 |
5 | 2 | 3 | 40 |
And I want a select returning a table with one element for user_id with one field for each category value (These are known and can only be 1, 2 or 3)
user_id | category_1 | category_2 | category_3 |
---|---|---|---|
1 | 30 | 40 | 10 |
2 | 0 | 0 | 40 |
The only solution I’ve found is:
select r.user_id as user_id, rt_c1.value as category_1_value, rt_c2.emissions_change_co2e as scope2_co2e_emissions, rt_c3.emissions_change_co2e as scope3_co2e_emissions from tablename as r join (select rt.user_id rt.category as category, sum(rt.value) as value from tablename as rt group by rt.user_id, rt.category) as rt_c1 on rt_c1.user_id = r.user_id and rt_c1.category = 1 join (select rt.user_id rt.category as category, sum(rt.value) as value from tablename as rt group by rt.user_id, rt.category) as rt_c2 on rt_c2.user_id = r.user_id and rt_c2.category = 2 join (select rt.user_id rt.category as category, sum(rt.value) as value from tablename as rt group by rt.user_id, rt.category) as rt_c3 on rt_c3.user_id = r.user_id and rt_c3.category = 3
Advertisement
Answer
Perhaps a simple conditional aggregation
Example
Select user_id ,sum( case when category=1 then value else 0 end) as category_1 ,sum( case when category=2 then value else 0 end) as category_2 ,sum( case when category=3 then value else 0 end) as category_3 From YourTable Group By user_id