Get the sum of child's tables fields in postgresql

SQL:

SELECT a.field_a,
       ( b_agg.field_b1_total + c_agg.field_c1_total ) total_sum
FROM   a
       LEFT OUTER JOIN (SELECT a1_id,
                               Sum(field_b1) field_b1_total
                        FROM   b
                        GROUP  BY a1_id) b_agg
                    ON a.id = b_agg.a1_id
       LEFT OUTER JOIN (SELECT a1_id,
                               Sum(field_c1) field_c1_total
                        FROM   c
                        GROUP  BY a1_id) c_agg
                    ON a.id = c_agg.a1_id; 

Output:

 field_a | total_sum
---------+-----------
     100 |       603
(1 row)

Tables:

create table a(id int, field_a int);
insert into a values(1,100);

create table b(id int, a1_id int, field_b1 int);
insert into b values(1,1,201);
insert into b values(2,1,202);

create table c(id int, a1_id int, field_c1 int);
insert into c values(1,1,200);

postgres=# select * from a;
 id | field_a
----+---------
  1 |     100
(1 row)

postgres=# select * from b;
 id | a1_id | field_b1
----+-------+----------
  1 |     1 |      201
  2 |     1 |      202
(2 rows)

postgres=# select * from c;
 id | a1_id | field_c1
----+-------+----------
  1 |     1 |      200
(1 row)

Edit : 2nd February 2022 - Making the query more performant

1. Since you have three tables , there is a need to access all three tables at least once , you can at best improve the access pattern.
2. Since you are outer joining on table A , you can materialize your needed data from table A in a CTE or TEMP table -- say for specific ID range
3. You can use the ID's that you get in the CTE in A to filter rows in B and then run the aggregate on B.
4. You can do the same with C , use the ID from the CTE on A.
5. If you have too many ID's from table A to be used in this SQL , then you will need to run this SQL for multiple ranges of ID's.
6. Indexes on A.id , B.a1_id and C.a1_id would help.

Goal:

• Is to execute group by on less data.

• Avoid full table scans on the 15 million rows table which I assume its table A as its the table you are outer joining to.

SQL: ( Divide & Conquer Run this sql by changing the range --- id BETWEEN 1 AND 100 , then 101-200, 201-300 , determine the range by testing and checking what is an acceptable response time , you might like to use pg_stat_statement or explain plan to get the right range for which there is no temp usage.).

WITH a_cte
     AS (SELECT a.field_a,
                a.id
         FROM   a
         WHERE  id BETWEEN 1 AND 100),
     b_cte_agg
     AS (SELECT a1_id,
                Sum(field_b1) field_b1_total
         FROM   b
         WHERE  a1_id IN (SELECT a_cte.id
                          FROM   a_cte) 
         GROUP  BY a1_id),
     c_cte_agg
     AS (SELECT a1_id,
                Sum(field_c1) field_c1_total
         FROM   c
         WHERE  a1_id IN (SELECT a_cte.id
                          FROM   a_cte) 
         GROUP  BY a1_id)
SELECT a_cte.field_a,
       ( b_cte_agg.field_b1_total
         + c_cte_agg.field_c1_total ) total_sum
FROM   a_cte
       LEFT OUTER JOIN b_cte_agg
                    ON a_cte.id = b_cte_agg.a1_id
       LEFT OUTER JOIN c_cte_agg 
                    ON a_cte.id = c_cte_agg.a1_id; 

Tested:

postgres=#
postgres=#
postgres=# WITH a_cte
postgres-#      AS (SELECT a.field_a,
postgres(#                 a.id
postgres(#          FROM   a
postgres(#          WHERE  id BETWEEN 1 AND 100),
postgres-#      b_cte_agg
postgres-#      AS (SELECT a1_id,
postgres(#                 Sum(field_b1) field_b1_total
postgres(#          FROM   b
postgres(#          WHERE  a1_id IN (SELECT a_cte.id
postgres(#                           FROM   a_cte)
postgres(#          GROUP  BY a1_id),
postgres-#      c_cte_agg
postgres-#      AS (SELECT a1_id,
postgres(#                 Sum(field_c1) field_c1_total
postgres(#          FROM   c
postgres(#          WHERE  a1_id IN (SELECT a_cte.id
postgres(#                           FROM   a_cte)
postgres(#          GROUP  BY a1_id)
postgres-# SELECT a_cte.field_a,
postgres-#        ( b_cte_agg.field_b1_total
postgres(#          + c_cte_agg.field_c1_total ) total_sum
postgres-# FROM   a_cte
postgres-#        LEFT OUTER JOIN b_cte_agg
postgres-#                     ON a_cte.id = b_cte_agg.a1_id
postgres-#        LEFT OUTER JOIN c_cte_agg
postgres-#                     ON a_cte.id = c_cte_agg.a1_id;
 field_a | total_sum
---------+-----------
     100 |       603
(1 row)

Retain Data from Multiple Iterations

To capture the data , you can define a temporary table and store the data for each iteration. For example:

create temp table finaldata(field_a int , total_sum int);
postgres=# create temp table finaldata(field_a int , total_sum int);
CREATE TABLE

Then repeat this for all the ID ranges from table A.

postgres=# WITH a_cte
postgres-#      AS (SELECT a.field_a,
postgres(#                 a.id
postgres(#          FROM   a
postgres(#          WHERE  id BETWEEN 1 AND 100),
postgres-#      b_cte_agg
postgres-#      AS (SELECT a1_id,
postgres(#                 Sum(field_b1) field_b1_total
postgres(#          FROM   b
postgres(#          WHERE  a1_id IN (SELECT a_cte.id
postgres(#                           FROM   a_cte)
postgres(#          GROUP  BY a1_id),
postgres-#      c_cte_agg
postgres-#      AS (SELECT a1_id,
postgres(#                 Sum(field_c1) field_c1_total
postgres(#          FROM   c
postgres(#          WHERE  a1_id IN (SELECT a_cte.id
postgres(#                           FROM   a_cte)
postgres(#          GROUP  BY a1_id)
postgres-# insert into finaldata SELECT a_cte.field_a,
postgres-#        ( b_cte_agg.field_b1_total
postgres(#          + c_cte_agg.field_c1_total ) total_sum
postgres-# FROM   a_cte
postgres-#        LEFT OUTER JOIN b_cte_agg
postgres-#                     ON a_cte.id = b_cte_agg.a1_id
postgres-#        LEFT OUTER JOIN c_cte_agg
postgres-#                     ON a_cte.id = c_cte_agg.a1_id;
INSERT 0 1
postgres=#

Final Data:

postgres=# select * from finaldata;
 field_a | total_sum
---------+-----------
     100 |       603
(1 row)