public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ibrahim Shaame <[email protected]>
To: swastik Gurung <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Reporting by family tree
Date: Wed, 25 Oct 2023 15:20:30 +0300
Message-ID: <CAJOWwD6pJocMJtNiR5nay2XX4dWDtj-WDwv4xgE7Lc+DymWO0g@mail.gmail.com> (raw)
In-Reply-To: <CAJOWwD46a+JVtnWkpNHs3ZDrEF7onrG39WryKm6X_rP=t7SHyg@mail.gmail.com>
References: <CAJOWwD5M=YK9nKBX6TD-NB1B7Ddo6uLZC2fG1WAED86fg4s2oA@mail.gmail.com>
<[email protected]>
<CAJOWwD7YmD8jPediD7qG49vq9mtTbJOx3eAV4a9234RTJe_aHw@mail.gmail.com>
<CAKFQuwaMRJUpMz8Usd3XHfymgdR=51G-CjOpi16nxWh+kytD0Q@mail.gmail.com>
<CAJOWwD6ED658ZAg80YK85sFq=vObnrj6f086tNug6C8=K7wn3w@mail.gmail.com>
<CAKFQuwYn6MfTyjpDraVqW7TfnCh2Q3-DvfEYHz+vifXvzpr_FA@mail.gmail.com>
<[email protected]>
<CAJOWwD46a+JVtnWkpNHs3ZDrEF7onrG39WryKm6X_rP=t7SHyg@mail.gmail.com>
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 <[email protected]> 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 <[email protected]>
> 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.
>>
>
view thread (13+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Reporting by family tree
In-Reply-To: <CAJOWwD6pJocMJtNiR5nay2XX4dWDtj-WDwv4xgE7Lc+DymWO0g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox