Given the following tree structure in a SQL table, and assuming the data is consistent (there are no rows with the same name, but different parents):
| name | parent | value |
|------|--------|-------|
| a | null | 10 |
| b | a | 15 |
| b | a | 4 |
| c | a | 15 |
| d | a | 10 |
| e | b | 5 |
| f | b | 5 |
| g | null | 20 |
I am looking for a query to sum up all sub-categories of a given node, like this:
| name | parent | value |
|------|--------|-------|
| a | null | 64 |
| b | a | 29 |
| c | a | 15 |
| d | a | 10 |
| e | b | 5 |
| f | b | 5 |
| g | null | 20 |
So, I can make only the first level of summation, and I can think of joining this to table itself on parent and and sum again... but I am looking for a solution for trees of unspecified depth. For the level 1 I have for example:
SELECT
NAME,
PARENT,
SUM(VALUE) AS VALUE
FROM
TEST
GROUP BY
NAME,
PARENT
ORDER BY
NAME ASC;
First, consolidate source table for (name,parent).
Then recursive collect all childs for every row.
See simple example
with recursive
gr as(select name,parent,sum(amount) amount
from test group by name,parent)
,r as(
select 1 lvl,name root,parent,name child,0::bigint total,amount
from gr
union all
select lvl+1 lvl,r.root,r.parent,t.name child,r.total+t.amount,t.amount
from r inner join gr t on t.parent=r.child
)
select root,parent,sum(amount)total
from r
group by root,parent
order by root
name | parent | total |
---|---|---|
a | null | 64 |
b | a | 29 |
c | a | 15 |
d | a | 10 |
e | b | 5 |
f | b | 5 |
g | null | 20 |
Test data:
create table test(name varchar(1),parent varchar(1), amount int);
insert into test14 values
('a',null, 10),
('b','a', 15),
('b','a', 4),
('c','a', 15),
('d','a', 10),
('e','b', 5),
('f','b', 5),
('g',null, 20);