Get the sum of child's tables fields in postgresql-CodePudding

I have table scheme as below:

Table A:

id  field_a1:integer   
1  100

Table B

 id  a1_id:integer  field_b1   
 1   1              201        
 2   1              202

Table C

id  a1_id:integer  field_c1   
1   1              200

I need sql query to get the SUM of Table A and B fields so result should be as bellow

SQL results(should be):

table_A_field_a1   total_sum(field_c1 field_b1)
100                603(200 201 202)

my sql query is

SELECT (SUM(field_c1) SUM(field_b1)) total_sum, a1.* 
FROM A as a 
LEFT JOIN B as b on b.a1_id = a.id
LEFT JOIN C as c on c.a1_id = a.id
GROUP BY a.id

But i am getting total_sum = 803 but it should be 603

CodePudding user response：

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)