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 1qsfIK-008rAt-Bd for pgsql-novice@arkaria.postgresql.org; Tue, 17 Oct 2023 08:19:16 +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 1qsfIH-007VYE-KF for pgsql-novice@arkaria.postgresql.org; Tue, 17 Oct 2023 08:19:14 +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 1qsfIH-007VY5-9S for pgsql-novice@lists.postgresql.org; Tue, 17 Oct 2023 08:19:14 +0000 Received: from mail-ed1-x529.google.com ([2a00:1450:4864:20::529]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qsfID-0018yZ-Hz for pgsql-novice@lists.postgresql.org; Tue, 17 Oct 2023 08:19:13 +0000 Received: by mail-ed1-x529.google.com with SMTP id 4fb4d7f45d1cf-533c5d10dc7so9370750a12.3 for ; Tue, 17 Oct 2023 01:19:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697530749; x=1698135549; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=BwO+9c0KTUHLkoxehSPiNtVYxYRRVqiv7hjx3Mih48g=; b=hMikqO7mosjRSvrs9VRA9frdMQv5ksPOMW2/u2CMY/8hU8ltgVxKkgdMJhs+qnHEoZ 2719QuRuxO37NGet/QFo6nOJ0xg5ubv8/9VIgKshKFgO2FpVn+K5QrXflVgj5v7bxQLW h/npFOx1Ju/OhR9AeQWuEl8tdErQkvnlA5YQ2bMKwv1wosSQ5C9ZpeEhbLrI0mymIQpc 1U2crY7fDCZJYNsJ2jD7wVZWz6daWkC6kvhJs5aPbU15q8OFT7uyru72Hbal5hUagRkG Ayst8ybXh4df6xNSVMhatEvK78PKQN0o3IlAeNJPDnz7LviMDE+f3GyIjMAB9h5s0IlJ NpCg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697530749; x=1698135549; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=BwO+9c0KTUHLkoxehSPiNtVYxYRRVqiv7hjx3Mih48g=; b=eWfdmiOKfcMkx2AouDUV6Uw+6SNQnjPBsvWUuhco1+LcsbXkgBijMqmd9dFkVbB6om rM9kRUrXT3HEmbtKYoW2xPPuduZOM/eLQGI1JT5RsHbhiqWGwM6BiUIZ0HBEgo5UbRPE bem01AiTM/C9yiaeR0t29LyRoD9B7h/vB/gttJtUHIl0BRL6MTWRtDWIBNEDug0GgLeN 5Jg34w4Czl1Y/NmJN1QsDo0IFd3BdI6FvhyPliGB5vU2FrBniFOw2FdwAuLuhFJJL1KH ruFEbdT2u13EZ1N8DyTDNbB4I6wWsUJ5rZYv7ItlPgOyEzlNkx/+xRf9JPCKT6VhrqrH dCbg== X-Gm-Message-State: AOJu0Yx8NO5FurHonFc2QqrXRKA4Ry1SRYnRzDM4vjSQ5sBB9utZoqEs W7hstQOhsI2MRwnwi6wgXbt1H7kxImKY9Q9cJ6w= X-Google-Smtp-Source: AGHT+IEbUq6DJW/tSCcGJa6iRWaQ1FCcfXgrpKdZhXvjfjLzoEQd2vliapIGmcm6vCzHCyegC69xe1TU1fOs2X3YmY8= X-Received: by 2002:a05:6402:278e:b0:53e:83a1:d45c with SMTP id b14-20020a056402278e00b0053e83a1d45cmr1141957ede.9.1697530749373; Tue, 17 Oct 2023 01:19:09 -0700 (PDT) MIME-Version: 1.0 References: <1901532185.8873313.1696511707263@mail.yahoo.com> <1653003450.12126241.1697462972371@mail.yahoo.com> In-Reply-To: <1653003450.12126241.1697462972371@mail.yahoo.com> From: Ibrahim Shaame Date: Tue, 17 Oct 2023 11:18:20 +0300 Message-ID: Subject: Re: Reporting by family tree To: swastik Gurung Cc: "pgsql-novice@lists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000004fd2630607e52d33" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004fd2630607e52d33 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFPM swastik Gurung 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 =3D c.contributor_id > union all > select > f.id, > f.parent_id, > f.name, > cte.contribution_amount > from > cte > join family f on > cte.id =3D 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 =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) > 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. > --0000000000004fd2630607e52d33 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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=E2=80=AFPM swastik Gurung <gurung_swastik@yahoo.com> wrote:
=
Example Below:

-- cr= eate a test family table
create table famil= y as
(
select
1 as id,
null::integer as parent_id,
=
'Grandfather1' as name
un= ion 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 pa= rent_id,
'Father1-2' as name
union all
select
5 = as id,
2 as parent_id,
'Father2-1' as name
union al= l
select
6 as id,
= 3 as parent_id,
&#= 39;Son1-1-1' as name
union all
select
<= div> 7 as id,
4 as = parent_id,
'Son1-2-1' as name<= /span>
union all
select
8= as id,
5 as parent_id,
'Son2-1-1' as name);

-- create a te= st contribution=C2=A0table
create table contribution as
(
select=
1 as contributor_id,
= '2020-01-01' as date,
<= /span>300.00 as contribution_amount
union all
se= lect
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 al= l
select
3 as contributor_id,
'2020-02-01' as date,
<= div> 200.00 as contribution_amount
unio= n all
select
4 as contributor_id,
'2020-03-01' as date,
150.00 as contribution_amount
= union all
select
4 as contributor_i= d,
'2020-04-01' as date,
60.45 as contribution_amount
union all
select
4 as contributo= r_id,
'2020-05-01' as date,
300.00 as contribution_amount
union all
select
5 as contri= butor_id,
'2020-06-01' as date= ,
1250.00 as contribution_amount
union all
select
6 as c= ontributor_id,
'2020-01-01' as= date,
66.50 as contribution_amount
union all
select
7 a= s contributor_id,
'2020-02-01'= as date,
855.00 as contribution_amoun= t
union all
select
8 as contributor_id,
'2020-02-01&= #39; as date,
25.00 as contribution_am= ount);

-- execute recursive que= ry, all children inheriting contribution sum of parents
with recu= rsive cte as
(
select
f.parent_id,
c.contribution_amount
from
family f
join contribution c on
= f.id =3D c.contributor= _id
union all
select
f.parent_id,
cte.contribution_amount
from
cte
join family f on
cte.id =3D f.parent_id= )
select
id,
name,
sum(contri= bution_amount) as total_contribution
from
= cte
group by
i= d,
name
order by
id;

=
-- execute recursive q= uery, parents have sum of all contributions of its children
with recursive cte as
(
select
<= span style=3D"outline:none">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.parent_id,
cte.contribution_amount
from
cte
join family f on
cte.parent_id =3D f.id)
select
id,
name,
sum(contribution_amount) as total_contribution
from
<= /span>cte
group by
id,
name
order by
id;


Change your SQL accordingly. Also, you can add month field to yi= eld results per month.
--0000000000004fd2630607e52d33--