Re: Reporting by family tree

Swastik, I have done as you suggested. What I get is:

id | name | total_contribution

----+--------------+--------------------

1 | Grandfather1 | 600.65

2 | Grandfather2 | 472.55

3 | Father1-1 | 800.65

4 | Father1-2 | 1111.10

5 | Father2-1 | 1722.55

6 | Son1-1-1 | 867.15

7 | Son1-2-1 | 1966.10

8 | Son2-1-1 | 1747.55


But what I want to get is  grandfather - father - children:

1 - Grandfather1

3 - father1-1

6 - son1-1

7 – son1-2

4 - Father1-2

8 - son2-1

2 – Grandfather2

5 - Father2-1

etc


Any suggestion
Thanks





On Tue, Oct 17, 2023 at 11:18 AM Ibrahim Shaame <ishaame@gmail.com> wrote:

> 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.
>>
>