Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qsNhJ-007nuy-K5 for pgsql-novice@arkaria.postgresql.org; Mon, 16 Oct 2023 13:31:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qsNhH-000uy9-4t for pgsql-novice@arkaria.postgresql.org; Mon, 16 Oct 2023 13:31:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qsNhG-000uy0-Oj for pgsql-novice@lists.postgresql.org; Mon, 16 Oct 2023 13:31:51 +0000 Received: from sonic309-20.consmr.mail.gq1.yahoo.com ([98.137.65.146]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qsNhC-000zIh-5D for pgsql-novice@lists.postgresql.org; Mon, 16 Oct 2023 13:31:50 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1697463103; bh=DB3GQqMs/uB4wN0Bmj4pFhCchsuCKYNUNgxAwO4rFyo=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=nJRpIc8uh/L4pt64TDzGAdf0Bnf4zoGUP+H2aIb5BMldH29/+AIUZfcZp7ECY/Jx1gM7s3U2vBqIj0hdlLVKp+b/BSz472IhzqWVRFu3x2kT0p/0PQ0FLh+6hlsvj3em/BDDLbOAxxKwZKJ7928DIL+uBHqn9hZhW+c+eO24Hb66zJ6aMfDBO8TBOSILcxMMFhdBAnKRpeFCwh0x+Mde4x9PKF5ympMbXLR9D4B7bA6Z2+D9VzwHoVLTEIODdbn72X3qJU25Ondr9zp2DllIfZV5rH+L5pejRzNOhWjE6ue/8TfZBfAstW/VLvWHNgrTAAmlxc4vhXuptC+M+HYRXQ== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1697463103; bh=FU/RTTnHZUORjcxMi2oNu6uJioZnzCWcPIOBBihTEFw=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=esDViVE5nExdY2CsPNOfj0k5bWEOI0eBy041R8ym+9mGaEfcrFnBK409T91y1lyuJOmIwhUCthVBG4BQNlK2vuJbobeDVV9vQyKoqZxNx1KrJvGvZbJN4WiUHvOODnqcUsIRvZX8iMMcWMPJKYF54JNIyee43OOyyepdGwo0J8e4819Ez9xmFjvOAiswOAuEL6s+DARz4zBlO1eGrTmrPsH9Il7qeZzXi4eQRNzv41XGa4IegIJL6Q92YLHL9y2KiaWqzmUoUQ7G2zlkju4mLDFN4RULvmuLcXrnKroGlfHSwDf46MFJ6kAtFDRoMZEjElMqhiNNnfjfG7ElnVNG+A== X-YMail-OSG: Khn0rTcVM1lO_ccFF8k.LUhsOubTcBUmfkQl9HW9M0oYDj9j8xXlf4HRXv1RQul LSxAUYIUjR3i4NUNYYq3YSRzJmxZRBBgrMgtMtCbnuZ3wUt017_jLMrTgz6SpRU5ndK7k0XxNM5Q Jh5UCfm.w72z.xrNcz0CY1Imu.d24xY1oPHd52h4iRyYmT.NikevjEBUUsbXhtFev0LW75c7jQHN qXEKKeGevMNuwQ6APUF0FzdT.ku_jIMdtrNEt.rlXIDEMkCUbaCNaCn2FMi8lrYETc3CvlVr6eD. _uIpMM_qUc4imCb7veCxnMMNjEV9IU1x_wUp9p4nK7OaVrX24wvZVPIWjrazxURFB0POxd6LTUQg KjV73jZMTQRpjTytiY449tecl9gCWJF9hBYhO1nuB8L7gIkZjJllZjVVIWtWlHVzHyFLoEL6AxDf rlVezIaMLtXwMyUy70KIm9psiDp9sG8za7QQWXKf5_bPds5.dSS5d6vNcGK4d5pYL9dcU_XB3M03 CLs8M0Akk0Q0LWLN.IQ.keRK1hRieSuJaEECxXm25DBhoR6tu5t.lEfQXGqCdwDeFVHBSCn72jg8 d6xx8LQPgbEGiX1.Q1O6Yf0J0ZCkW6Xo.7Lb_iFm5JmuAXmXyeo3XtXIWjRDrqV7b0xqvilGevQR mCBGA9O6fMQ0jIsgHgjToUG4ecRewSyocVM3lcMcCr_DMnyoTTvqzsCvOEw2dnos3kRdvZKd1.fu c5WEeeOFIwdWKiKk97uUh1BJu_2owuDWqneCyUfOcd3pFQZLojk3BGWpzPvee0v36gJv1yn0zjal HUvhGqs1KEDgqoKJPAFNUgoQoQ0jrHxaLdDYFVh4ANgR5alBCuJqywymruKAQ_06PupO66whFtEQ Oa6Md__.4H35attrjfFIsS0R5a8.OJLOXpuunPoH9KhmG1ij1NUGRi7Ijub3IMMicypM7KsKYKw7 F_q_AtsBI8klDhUHmQvjyIJfqWtR.QRfGxORFsTcjRIWNtt3pSoarqONRZlHuXwnCCwKf8ffdIRT 7tr2HYMdveH3uv94d.qf1GXuFZsUxTuVRDXvqLi0vCGgIpkr60vZLWQiGQnVOJ.jrBKbBiAM0qhW Cg6U2oHF6Pow7WRd7ogv.tsXItnVmnolKdVy13o93._cBYZ4S0hBoXAzH.2pJvbc0cd.wPIKP3Z2 w4wE7HF_y5bUQpo38oKxqQtgrf5c0OLrQjaO.vU6efZ0jgOv1304ASt4kwVggMs1FF3EAVEQrEkv NIf5dc7.EdeYy0r6KUZ8bodBEmACvs_9OIsosVpWrtveqrliKD69fvGDa8JbDMqUq4wRRwo2N0Sd JSsDwlirGZwctuiitog0NurUcX3v9JlFHltS8MzVVnrIqQC9VVgFmEUfio4wtNuV7dC6hIlcqu3o bku5MAJegLGP.cvqJrXUlcfylEgDofN.sLU0w5TeuBG6WLtAhmuz4_oLZTyJFdOQo8T5k_zr9QM_ gIKXbgdPz3XyhMCkRjhdMu8aM2TqqVQk3S8qPEVHx3tUd_yzg0dHKrZ8juydIp5HMFPl1sa1tlGR VuElZ_pURiUBQFuaoFpVEXiIk.Ln1EPO0XO1UgWju458eHhF4RLt4Xu2oewF_ktY4A4DGv9Qd_00 JaH4WjTiMN4rpb26LyTm8h8opHvOS0bMYbXlj0gkL4CNahah.W_aDyJU28KJTY0Wb_X8QmEvf7Oe b3pLHrPI0a7hYdfKTwsTAC3szAbYIU8_nnT_MoZVsglFHOBMNUmq4IwwOa4dz7zzeo0aEjwp2XFe cxA9_fxbhYAK2..BA20Ka4x9NhPcssO4LFZEM9EFJVBIjMrXIX2Hh3EMIbuayD2Lw4RqYAYULqcH Ok2GNWijQnvDwtkGJrlkjolrjpnZxwvI_ZCWcLMHBKuXlk9LsdVpxCNDb.f_28kQNpfYWjJNhWVt 04z5OKvGtGEpJsQEhelj46qkst76HlRv6PJZmxtAMP1ZMG6But7uLU6m7Z4AZko.2U8j6_4Q1Htx Qr1WDeH2ioANT8DiE3AJabLznKWije8qHnEP1NxEkykzt0qPZlQ60_F6MxMWa3cfIUT2G2fW_Kl5 G1TrAmxg3K2cO.YF5UC2pfAhwhqOuXeqY0X0ASBdKEX7MP4xpiNudTpCLkCRghWP9hFNZDlDn.h3 XnYyrdJL.KAK1TiFOvEuwPw.B7tBZcTtkVzz2_kDPanYEG_VeOnBGSqstBsitGwGV75ykzYu3gs4 98K2sW_dA0ZBWJirZEaGGNfsKFvhEN8HKbjYPCw-- X-Sonic-MF: X-Sonic-ID: 8057d3e4-b410-4888-95de-f1b4513abd5c Received: from sonic.gate.mail.ne1.yahoo.com by sonic309.consmr.mail.gq1.yahoo.com with HTTP; Mon, 16 Oct 2023 13:31:43 +0000 Date: Mon, 16 Oct 2023 13:29:32 +0000 (UTC) From: swastik Gurung To: Ibrahim Shaame Cc: "pgsql-novice@lists.postgresql.org" Message-ID: <1653003450.12126241.1697462972371@mail.yahoo.com> In-Reply-To: References: <1901532185.8873313.1696511707263@mail.yahoo.com> Subject: Re: Reporting by family tree MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_12126240_626708934.1697462972366" X-Mailer: WebService/1.1.21797 YMailNorrin Content-Length: 17380 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_12126240_626708934.1697462972366 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Example Below: -- create a test family tablecreate table family as(select 1 as id, null::i= nteger as parent_id, 'Grandfather1' as nameunion allselect 2 as id, null::i= nteger as parent_id, 'Grandfather2' as nameunion allselect 3 as id, 1 as pa= rent_id, 'Father1-1' as nameunion allselect 4 as id, 1 as parent_id, 'Fathe= r1-2' as nameunion allselect 5 as id, 2 as parent_id, 'Father2-1' as nameun= ion 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 pare= nt_id, 'Son2-1-1' as name); -- create a test contribution=C2=A0tablecreate table contribution as(select= 1 as contributor_id, '2020-01-01' as date, 300.00 as contribution_amountun= ion allselect 1 as contributor_id, '2020-02-01' as date, 255.00 as contribu= tion_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, '20= 20-01-01' as date, 450.00 as contribution_amountunion allselect 3 as contri= butor_id, '2020-02-01' as date, 200.00 as contribution_amountunion allselec= t 4 as contributor_id, '2020-03-01' as date, 150.00 as contribution_amountu= nion allselect 4 as contributor_id, '2020-04-01' as date, 60.45 as contribu= tion_amountunion allselect 4 as contributor_id, '2020-05-01' as date, 300.0= 0 as contribution_amountunion allselect 5 as contributor_id, '2020-06-01' a= s date, 1250.00 as contribution_amountunion allselect 6 as contributor_id, = '2020-01-01' as date, 66.50 as contribution_amountunion allselect 7 as cont= ributor_id, '2020-02-01' as date, 855.00 as contribution_amountunion allsel= ect 8 as contributor_id, '2020-02-01' as date, 25.00 as contribution_amount= ); -- execute recursive query, all children inheriting contribution sum of par= entswith recursive cte as(select f.id, f.parent_id, f.name, c.contribution_= amountfrom family fjoin contribution c on f.id =3D c.contributor_idunion al= lselect f.id, f.parent_id, f.name, cte.contribution_amountfrom ctejoin fami= ly f on cte.id =3D 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 ch= ildrenwith recursive cte as(selectf.id,f.parent_id,f.name,c.contribution_am= ountfromfamily fjoin contribution c onf.id =3D c.contributor_idunion allsel= ectf.id,f.parent_id,f.name,cte.contribution_amountfromctejoin family f onct= e.parent_id =3D f.id)selectid,name,sum(contribution_amount) as total_contri= butionfromctegroup byid,nameorder byid; Change your SQL accordingly. Also, you can add month field to yield results= per month. ------=_Part_12126240_626708934.1697462972366 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Example Below:

-- create a= test family table
cr= eate table family as
(
select
=09= 1 as id,
=09null::integer as par= ent_id,
=09'Grandfather1' as name
union all
select
=092 as= id,
=09null::integer as parent_id,
=09'Grandfather2' as name
union all
select
=093 as id,=
=091 as parent_id,
=09'Father1-1' as name
union all
selec= t
=094 as id,
=09= 1 as parent_id,
=09'Father1-2' a= s name
union all
select
= =095 as id,
=092 as parent_id,
=09'Father2-1' as name
= union all
select
=096 as id,<= /div>
=093 as parent_id,
=09'Son1-1-1' as name
union all
select<= /div>
=097 as id,
=094 as parent_id,
=09'Son1-2-1' as n= ame
union all
select
=098 as id,
=095 as parent_id,=
=09'Son2-1-1' as name);

=
-- create a test contribution table
create table contribution as
(
select
= =091 as contributor_id,
=09= '2020-01-01' as date,
=09300.00 = as contribution_amount
union all
select
=091 as contributor_id,
= =09'2020-02-01' as date,
=09255.= 00 as contribution_amount
union all
select
=
=091 as contributor_id,
=09'2020-03-01' as date,
=0945= .65 as contribution_amount
union all
select
=092 as contributor_id,
=09'2020-05-01' as date,
=092= 2.55 as contribution_amount
union all
select
=092 as contributor_id,
=09'2020-01-01' as date,
=09= 450.00 as contribution_amount
union all
select
=093 as contributor_id,
= =09'2020-02-01' as date,
=09200.00 as contribution_amount
union all
select=
=094 as contributor_id,
=09'2020-03-01' as date,
=09150.00 as contribution_amount
union all
sele= ct
=094 as contributor_id,
=09'2020-04-01' as date,
=09<= /span>60.45 as contribution_amount
union all
sel= ect
=094 as contributor_id,
<= span>=09'2020-05-01' as date,
=09= 300.00 as contribution_amount
union all
s= elect
=095 as contributor_id,
=09'2020-06-01' as date,
= =091250.00 as contribution_amount
union all
select
=096 as contributor_id,
=
=09'2020-01-01' as date,
=0966.50 as contribution_amount
union all
<= div>select
=097 as contributor_id,
=09'2020-02-01' as date,
=09855.00 as contribution_amount
union all
select
=098 as contributor_id,
=09'2020-02-01' as date,
= =0925.00 as contribution_amount);

<= div dir=3D"ltr" data-setdir=3D"false">-- execute recursive query, all child= ren inheriting contribution sum of parents
with recursive cte as<= /div>
(
select
=09f.id,
=09f.parent_id,
= =09f.name,
=09c.contribution_amo= unt
from
=09family f
join contribution c on
=09f.id =3D c.= contributor_id
union all
select
= =09f.id,
=09f.parent_id,
=09f.name,
= =09cte.contribution_amount
from
=09cte
join family f on
= =09cte.id =3D f.parent_id)
select
<= span>=09id,
=09name,
=09sum(contribution_amount) as total_contribution<= /span>
from
=09cte
= group by
=09id,
= =09name
order by
=09id= ;

<= div dir=3D"ltr" data-setdir=3D"false" style=3D"color: rgb(0, 0, 0); font-fa= mily: "Helvetica Neue", Helvetica, Arial, sans-serif; outline: no= ne !important;">-- execute recursive query, parents have sum of all contrib= utions 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 =3D 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 =3D= f.id)
id,
name,
sum(contribution_amount) as to= tal_contribution
from
cte
group by
id,
<= span style=3D"outline: none !important;">name
order by
id;


Change your SQL accordingly. Also, you can a= dd month field to yield results per month.
------=_Part_12126240_626708934.1697462972366--