Re: Reporting by family tree - Mailing list pgsql-novice
| From | swastik Gurung |
|---|---|
| Subject | Re: Reporting by family tree |
| Date | |
| Msg-id | 1653003450.12126241.1697462972371@mail.yahoo.com Whole thread Raw |
| In response to | Re: Reporting by family tree ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Responses |
Re: Reporting by family tree
|
| List | pgsql-novice |
Example Below:
-- create a test family table
create table family as
(
select
1 as id,
null::integer as parent_id,
'Grandfather1' as name
union all
select
2 as id,
null::integer as parent_id,
'Grandfather2' as name
union all
select
3 as id,
1 as parent_id,
'Father1-1' as name
union all
select
4 as id,
1 as parent_id,
'Father1-2' as name
union all
select
5 as id,
2 as parent_id,
'Father2-1' as name
union all
select
6 as id,
3 as parent_id,
'Son1-1-1' as name
union all
select
7 as id,
4 as parent_id,
'Son1-2-1' as name
union all
select
8 as id,
5 as parent_id,
'Son2-1-1' as name);
-- create a test contribution table
create table contribution as
(
select
1 as contributor_id,
'2020-01-01' as date,
300.00 as contribution_amount
union all
select
1 as contributor_id,
'2020-02-01' as date,
255.00 as contribution_amount
union all
select
1 as contributor_id,
'2020-03-01' as date,
45.65 as contribution_amount
union all
select
2 as contributor_id,
'2020-05-01' as date,
22.55 as contribution_amount
union all
select
2 as contributor_id,
'2020-01-01' as date,
450.00 as contribution_amount
union all
select
3 as contributor_id,
'2020-02-01' as date,
200.00 as contribution_amount
union all
select
4 as contributor_id,
'2020-03-01' as date,
150.00 as contribution_amount
union all
select
4 as contributor_id,
'2020-04-01' as date,
60.45 as contribution_amount
union all
select
4 as contributor_id,
'2020-05-01' as date,
300.00 as contribution_amount
union all
select
5 as contributor_id,
'2020-06-01' as date,
1250.00 as contribution_amount
union all
select
6 as contributor_id,
'2020-01-01' as date,
66.50 as contribution_amount
union all
select
7 as contributor_id,
'2020-02-01' as date,
855.00 as contribution_amount
union all
select
8 as contributor_id,
'2020-02-01' as date,
25.00 as contribution_amount);
-- execute recursive query, all children inheriting contribution sum of parents
with recursive cte as
(
select
f.id,
f.parent_id,
f.name,
c.contribution_amount
from
family f
join contribution c on
f.id = c.contributor_id
union all
select
f.id,
f.parent_id,
f.name,
cte.contribution_amount
from
cte
join family f on
cte.id = f.parent_id)
select
id,
name,
sum(contribution_amount) as total_contribution
from
cte
group by
id,
name
order by
id;
-- execute recursive query, parents have sum of all contributions of its children
with recursive cte as
(
select
f.id,
f.parent_id,
f.name,
c.contribution_amount
from
family f
join contribution c on
f.id = c.contributor_id
union all
select
f.id,
f.parent_id,
f.name,
cte.contribution_amount
from
cte
join family f on
cte.parent_id = f.id)
select
id,
name,
sum(contribution_amount) as total_contribution
from
cte
group by
id,
name
order by
id;
Change your SQL accordingly. Also, you can add month field to yield results per month.
pgsql-novice by date: