Re: Reporting by family tree
От | Ibrahim Shaame |
---|---|
Тема | Re: Reporting by family tree |
Дата | |
Msg-id | CAJOWwD46a+JVtnWkpNHs3ZDrEF7onrG39WryKm6X_rP=t7SHyg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Reporting by family tree (swastik Gurung <gurung_swastik@yahoo.com>) |
Ответы |
Re: Reporting by family tree
(Ibrahim Shaame <ishaame@gmail.com>)
|
Список | 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 tablecreate table family as(select1 as id,null::integer as parent_id,'Grandfather1' as nameunion allselect2 as id,null::integer as parent_id,'Grandfather2' as nameunion allselect3 as id,1 as parent_id,'Father1-1' as nameunion allselect4 as id,1 as parent_id,'Father1-2' as nameunion allselect5 as id,2 as parent_id,'Father2-1' as nameunion allselect6 as id,3 as parent_id,'Son1-1-1' as nameunion allselect7 as id,4 as parent_id,'Son1-2-1' as nameunion allselect8 as id,5 as parent_id,'Son2-1-1' as name);-- create a test contribution tablecreate table contribution as(select1 as contributor_id,'2020-01-01' as date,300.00 as contribution_amountunion allselect1 as contributor_id,'2020-02-01' as date,255.00 as contribution_amountunion allselect1 as contributor_id,'2020-03-01' as date,45.65 as contribution_amountunion allselect2 as contributor_id,'2020-05-01' as date,22.55 as contribution_amountunion allselect2 as contributor_id,'2020-01-01' as date,450.00 as contribution_amountunion allselect3 as contributor_id,'2020-02-01' as date,200.00 as contribution_amountunion allselect4 as contributor_id,'2020-03-01' as date,150.00 as contribution_amountunion allselect4 as contributor_id,'2020-04-01' as date,60.45 as contribution_amountunion allselect4 as contributor_id,'2020-05-01' as date,300.00 as contribution_amountunion allselect5 as contributor_id,'2020-06-01' as date,1250.00 as contribution_amountunion allselect6 as contributor_id,'2020-01-01' as date,66.50 as contribution_amountunion allselect7 as contributor_id,'2020-02-01' as date,855.00 as contribution_amountunion allselect8 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(selectf.id,f.parent_id,c.contribution_amountfromfamily fjoin contribution c onf.id = c.contributor_idunion allselectf.id,f.parent_id,cte.contribution_amountfromctejoin family f oncte.id = f.parent_id)selectid,name,sum(contribution_amount) as total_contributionfromctegroup byid,nameorder byid;-- execute recursive query, parents have sum of all contributions of its childrenwith recursive cte as(selectf.id,f.parent_id,c.contribution_amountfromfamily fjoin contribution c onf.id = c.contributor_idunion allselectf.id,f.parent_id,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.
В списке pgsql-novice по дате отправления: