CODEWITHSUNDEEP
sqlpostgresql
Alireza
Alireza

Reputation: 420

PostgreSQL group by on different columns

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.

Upvotes: 1

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

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);

Upvotes: 1

Related Questions