PostgreSQL group by on different columns

user_7231796 0
Viewed 35

I have a table as the following:

branch_id parent_branch_id sales
1 10
2 1 5
3 8
4 3 3
5 1 1
6 6

I need to aggregate the table on branches of a father branch

branch_id sales
1 16
3 11
6 6

What I have tried:

I tried first separating the branches which have a parent branch and aggregating them on their parent_branch_id, then joining this aggregated table with the table of the parent only branches on t1.parent_branch_id=t2.branch_id and summing the resulting columns to get total sales.

I feel this, having to join tables, is costly and maybe there is a smarter way to do it with builtin PostgreSQL functions.

postgresql sql
2 Answers

Assuming that we only need to consider two levels, parents and immediate children, we can try a self join approach here:

SELECT COALESCE(t1.parent_branch_id, t1.branch_id) AS branch_id,
       SUM(t1.sales) AS sales
FROM yourTable t1
LEFT JOIN yourTable t2
    ON t2.branch_id = t1.parent_branch_id
GROUP BY
    COALESCE(t1.parent_branch_id, t1.branch_id);
user_18632290

You don't need a join for this:

select coalesce(parent_branch_id, branch_id) as branch_id, 
       sum(sales)
from t
group by coalesce(parent_branch_id, branch_id)
order by branch_id;

Here is a db<>fiddle.

user_11440350
Related