public inbox for [email protected]
help / color / mirror / Atom feedFrom: swastik Gurung <[email protected]>
To: Ibrahim Shaame <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Reporting by family tree
Date: Mon, 16 Oct 2023 13:29:32 +0000 (UTC)
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwYn6MfTyjpDraVqW7TfnCh2Q3-DvfEYHz+vifXvzpr_FA@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>
Example Below:
-- create a test family tablecreate table family as(select 1 as id, null::integer as parent_id, 'Grandfather1' as nameunion allselect 2 as id, null::integer as parent_id, 'Grandfather2' as nameunion allselect 3 as id, 1 as parent_id, 'Father1-1' as nameunion allselect 4 as id, 1 as parent_id, 'Father1-2' as nameunion allselect 5 as id, 2 as parent_id, 'Father2-1' as nameunion allselect 6 as id, 3 as parent_id, 'Son1-1-1' as nameunion allselect 7 as id, 4 as parent_id, 'Son1-2-1' as nameunion allselect 8 as id, 5 as parent_id, 'Son2-1-1' as name);
-- create a test contribution tablecreate table contribution as(select 1 as contributor_id, '2020-01-01' as date, 300.00 as contribution_amountunion allselect 1 as contributor_id, '2020-02-01' as date, 255.00 as contribution_amountunion allselect 1 as contributor_id, '2020-03-01' as date, 45.65 as contribution_amountunion allselect 2 as contributor_id, '2020-05-01' as date, 22.55 as contribution_amountunion allselect 2 as contributor_id, '2020-01-01' as date, 450.00 as contribution_amountunion allselect 3 as contributor_id, '2020-02-01' as date, 200.00 as contribution_amountunion allselect 4 as contributor_id, '2020-03-01' as date, 150.00 as contribution_amountunion allselect 4 as contributor_id, '2020-04-01' as date, 60.45 as contribution_amountunion allselect 4 as contributor_id, '2020-05-01' as date, 300.00 as contribution_amountunion allselect 5 as contributor_id, '2020-06-01' as date, 1250.00 as contribution_amountunion allselect 6 as contributor_id, '2020-01-01' as date, 66.50 as contribution_amountunion allselect 7 as contributor_id, '2020-02-01' as date, 855.00 as contribution_amountunion allselect 8 as contributor_id, '2020-02-01' as date, 25.00 as contribution_amount);
-- execute recursive query, all children inheriting contribution sum of parentswith recursive cte as(select f.id, f.parent_id, f.name, c.contribution_amountfrom family fjoin contribution c on f.id = c.contributor_idunion allselect f.id, f.parent_id, f.name, cte.contribution_amountfrom ctejoin family f on cte.id = f.parent_id)select id, name, sum(contribution_amount) as total_contributionfrom ctegroup by id, nameorder by id;
-- execute recursive query, parents have sum of all contributions of its childrenwith recursive cte as(selectf.id,f.parent_id,f.name,c.contribution_amountfromfamily fjoin contribution c onf.id = c.contributor_idunion allselectf.id,f.parent_id,f.name,cte.contribution_amountfromctejoin family f oncte.parent_id = f.id)selectid,name,sum(contribution_amount) as total_contributionfromctegroup byid,nameorder byid;
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: <[email protected]>
* 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