Re: Reporting by family tree - Mailing list pgsql-novice

Thank you David and Swastik, Swastik, I will work on it and will let you know.

Thanks again for your help

On Mon, Oct 16, 2023 at 4:31 PM swastik Gurung <gurung_swastik@yahoo.com> wrote:

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.