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 1qvct8-006IQK-Aw for pgsql-novice@arkaria.postgresql.org; Wed, 25 Oct 2023 12:21:30 +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 1qvct6-003FVI-RM for pgsql-novice@arkaria.postgresql.org; Wed, 25 Oct 2023 12:21:28 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qvct6-003FUn-7v for pgsql-novice@lists.postgresql.org; Wed, 25 Oct 2023 12:21:28 +0000 Received: from mail-ed1-x535.google.com ([2a00:1450:4864:20::535]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qvct3-002EpO-5n for pgsql-novice@lists.postgresql.org; Wed, 25 Oct 2023 12:21:27 +0000 Received: by mail-ed1-x535.google.com with SMTP id 4fb4d7f45d1cf-53df747cfe5so8852569a12.2 for ; Wed, 25 Oct 2023 05:21:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1698236483; x=1698841283; 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=d28z6lnKtqpF4KtfbH3dMFWowRshDgkBezc7P5vxaic=; b=mmPgy6ln2zt+7/80efACImZRAFNhwyNC06G3FoilVAIE4oFQZTpFapTJtf3f71BUP0 cWOeUlSPgWbA3159fN9iI/lke5eV58XkFv4swbSXTG4xvgkSU/SfolFC3K0zIrCnqTym HrvvY66hJ2GzrasQrSe3F4i4AXQtsMtPQ91IUP5IKYmuZZkge1ELVG07EP/jlKvpXDUs Z7CHG7kY5fCbQQGJCY4eHWoUsiDdFndONg/RGIIKipdktNZbGVvPKo8XsRqV2Y/cnMfU a+uqB9OvdrqJ75396MiZTCju4E1Csn4CzGTKmEHRy2wKy1VmJZg0Rxc8aNcfKhqFRraV 5M+g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1698236483; x=1698841283; 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=d28z6lnKtqpF4KtfbH3dMFWowRshDgkBezc7P5vxaic=; b=bOm0VP7cHDwSwhej7nTwj2V3ltIgqhrYfu/xl42qXLMiBmrWq4vXqpi83KxP+Ash1m hlK1QGiv1Gar9/0Op/TfnF1B3smuQEjhJfl39G/kevWngaGMvuHfG5EJtziDQAhy8O84 fRTSHv3KBbHOrH4JeZm8zeKG/T+gbBLLodnUA5qTJ0gSibSBDvJWBLXjCw490ZNIldCf byvCFaT4rtJRU1TPq2PWUVhil+8422RWqiPqj9FM6rcn9LnBrL3zsvy97bxaPoRXeNN9 7Ph1Zk7cdztcgl0sc7uWp/zsRo6zozr5+nOAY+QIe5YiSRdKeQhrrMwV9OuH9a5fqhMd UaKw== X-Gm-Message-State: AOJu0YwTa5VC6fSWjS/uqM0IzlN1hKJWnZh98v8MRcaEn81iOEoWceQS K2o+Zg0aL6CCjIF/g6rHOVP1mm0WL4A8IhjyXic= X-Google-Smtp-Source: AGHT+IF0AaCVZWZrKwLeRLRcwky/7DePt2Uef/dnoQu7wYRQh9YsQ7QxPRhlcXFLM1jzd5HVYKrYAjT/sfm6UgvCKI8= X-Received: by 2002:a05:6402:350b:b0:53e:343a:76c4 with SMTP id b11-20020a056402350b00b0053e343a76c4mr12315921edd.30.1698236483122; Wed, 25 Oct 2023 05:21:23 -0700 (PDT) MIME-Version: 1.0 References: <1901532185.8873313.1696511707263@mail.yahoo.com> <1653003450.12126241.1697462972371@mail.yahoo.com> In-Reply-To: From: Ibrahim Shaame Date: Wed, 25 Oct 2023 15:20:30 +0300 Message-ID: Subject: Re: Reporting by family tree To: swastik Gurung Cc: "pgsql-novice@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000523a4c0608897ebc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000523a4c0608897ebc Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 =E2=80=93 son1-2 4 - Father1-2 8 - son2-1 2 =E2=80=93 Grandfather2 5 - Father2-1 etc Any suggestion Thanks On Tue, Oct 17, 2023 at 11:18=E2=80=AFAM Ibrahim Shaame = 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=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. >> > --000000000000523a4c0608897ebc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Swastik, I have done as you suggested. What I get is:=

=09 =09 =09

id | name | total_contribution=20

----= +--------------+--------------------

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=C2=A0 grandfather - father = - children:

=09 =09 =09

1 - Grandfather1

3 - = father1-1

6 - = son1-1

7 = =E2=80=93 son1-2

4 - = Father1-2

8 - = son2-1

2 = =E2=80=93 Grandfather2

5 - = Father2-1=20

etc<= /p>


Any suggestion
Thanks





On Tue, Oct 17, 2023 at 11:18=E2=80=AFAM Ibrahim Shaame <ishaame@gmail.com> 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= =E2=80=AFPM swastik Gurung <gurung_swastik@yahoo.com> wrote:
Ex= ample Below:

-- create a t= est family table
create table family as
(
select
1 as id,
null::integer as parent_id,
'Grandfather1' as name
union all<= /div>
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
<= div>select
6 as id,
3 as parent_id,
'Son1-= 1-1' as name
union all
select
7 as id,
4 as parent_i= d,
'Son1-2-1' as name
union all
select
8 as id,<= /span>
5 as parent_id,
'Son2-1-1' as name);

-- create a test contribution=C2=A0table
<= /div>
create table contribution as
(
select
1 as contributor_id,
<= /span>'2020-01-01' as date,
30= 0.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,
<= /span>45.65 as contribution_amount
union all
sel= ect
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
<= div>select
3 as contributor_id,
<= div> '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 al= l
select
4 as contributor_id,
'2020-04-01' as date,
<= div> 60.45 as contribution_amount
union= all
select
4 as contributor_id,
'2020-05-01' as date,
300.00 as contribution_amount
u= nion all
select
5 as contributor_id= ,
'2020-06-01' as date,=
1250.00 as contribution_amount
<= div>union all
select
6 as contribut= or_id,
'2020-01-01' as date,
66.50 as contribution_amount
union all
select
7 as contri= butor_id,
'2020-02-01' as date= ,
855.00 as contribution_amount=
union all
select
8 as co= ntributor_id,
'2020-02-01' as = date,
25.00 as contribution_amount);

-- execute recursive query, all = children inheriting contribution sum of parents
with recursive ct= e as
(
select
f.parent_id,
c.co= ntribution_amount
from
famil= y f
join contribution c on
<= a href=3D"http://f.id" target=3D"_blank">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)<= /div>
select
id,
<= span> name,
sum(contribution_am= ount) as total_contribution
from
cte
group by
id,=
name
order by
id;

-- execute recursive query, par= ents have sum of all contributions of its children
with recursive cte as
(
select
=
f.parent_id,
c.contribution_amount
from
family f
=
join contribution c on
f.id =3D c.co= ntributor_id
union all
select
= f.id,
f.parent_id,
cte.contribution_amount
from
cte
join family f on
cte.p= arent_id =3D f.id)
select
id,
name,
sum(contribution_amount) as total_contribution
from
cte
group by
id,
name
order by
id;=


Ch= ange your SQL accordingly. Also, you can add month field to yield results p= er month.
--000000000000523a4c0608897ebc--