SQL: simple summation query with join to 2 other tables

I have 3 tables in sqlite as follows:

Table 1

ID | x
1, 2.0
2, 3.0
3, 4.0
4, 3.0

Table 2

join_ID | x
1, 5.0
1, 6.0
2, 5.0
2, 2.0
3, 2.0
4, 2.0

Table 3

join_ID | x
4, 5.0
1, 6.0
3, 5.0
2, 2.0
2, 2.0
1, 2.0

I would like to obtain a table as follows:

ID | x | x_agg
1, 2, 21
2, 3, 14
3, 4, 11
4, 3, 10

Where x represents the sum of all rows containing coinciding ID's. In essence this is the computation I would like to achieve:

row1 --> 2+5+6+6+2 = 21

row2 --> 3+5+2+2+2 = 14

row3 --> 4+2+5 = 11

row4 --> 3+2+5 = 10

I am using sqlite within the DBManager in QGIS (via spatialite). The above is a limited working example I have concocted but should replicate the conditions I am working under. I came up with the following code:

 select
  table1.ID,
  ifnull(table1.x,0) as x,
  SUM(ifnull(table2.x,0)) +SUM(ifnull(table3.x,0))+ifnull(table1.x,0) as x_agg
from
 table1
  left join table2 on table1.ID = table2.join_ID
  left join table3 on table1.ID = table3.join_ID
group by
  ID;

But instead get:

ID  x   x_agg
1   2.0 40.0
2   3.0 25.0
3   4.0 11.0
4   3.0 10.0

When I run the above. What am I doing wrong here?

Solution

Probably the easiest way to proceed here is to just join to two separate subqueries which aggregate the second and third tables separately:

SELECT
    t1.ID,
    t1.x,
    COALESCE(t2.sum_x, 0) + COALESCE(t3.sum_x, 0) AS x_agg
FROM table1 t1
LEFT JOIN
(
    SELECT join_ID, SUM(x) AS sum_x
    FROM table2
    GROUP BY join_ID
) t2
    ON t1.ID = t2.join_ID
LEFT JOIN
(
    SELECT join_ID, SUM(x) AS sum_x
    FROM table3
    GROUP BY join_ID
) t3
    ON t1.ID = t3.join_ID;

Note that I use left joins in both places because it could be that a given ID from the first table does not appear in either of the two other tables. In this case, the effective sum should be treated as zero.