SQL: simple summation query with join to 2 other tables

Question

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?

score 1 · Accepted Answer · answered Apr 22 '19 at 10:33

1

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.

answered Apr 22 '19 at 10:33

Tim Biegeleisen

502,043
27
286
360

seems to work... what is the difference between `COALESCE` and `IFNULL`? Why are you using `COALESCE` instead of `IFNULL` here? – user32882 Apr 22 '19 at 10:56
@user32882 The only difference between `IFNULL` and `COALESCE` in SQLite is that the former accepts only two arguments, while the latter accepts more than two. You could replace `COALESCE` with `IFNULL` in my query and it would be the same. – Tim Biegeleisen Apr 22 '19 at 10:59
Fair enough. Accepted. – user32882 Apr 22 '19 at 11:02
That gives me different results than what the OP wanted for the `x_agg` column. – Shawn Apr 22 '19 at 11:35
@Shawn Agreed, but then I don't know what the logic is for aggregation. – Tim Biegeleisen Apr 22 '19 at 11:51

Shawn · Answer 2 · 2019-04-22T11:43:57.177

What about this one, which adds the sums of the tables all in the row part of the outer SELECT without any joins?

SELECT id
     , ifnull(x, 0) as x
     , ifnull(x, 0)
         + (SELECT total(x) FROM table2 AS t2 WHERE t1.id = t2.join_id)
         + (SELECT total(x) FROM table3 AS t3 WHERE t1.id = t3.join_id) AS x_agg
FROM table1 AS t1
ORDER BY id;
id          x           x_agg     
----------  ----------  ----------
1           2           21.0      
2           3           14.0      
3           4           11.0      
4           3           10.0

sum() vs total(): Both return the sum of all non-null values, but sum() returns null and total() returns 0.0 for the case where every value is null.

Using the following data (Note use of indexes to improve the correlated subqueries):

CREATE TABLE table1(id INTEGER PRIMARY KEY, x NUMERIC);
INSERT INTO table1 VALUES(1,2);
INSERT INTO table1 VALUES(2,3);
INSERT INTO table1 VALUES(3,4);
INSERT INTO table1 VALUES(4,3);
CREATE TABLE table2(join_id INTEGER, x NUMERIC);
INSERT INTO table2 VALUES(1,5);
INSERT INTO table2 VALUES(1,6);
INSERT INTO table2 VALUES(2,5);
INSERT INTO table2 VALUES(2,2);
INSERT INTO table2 VALUES(3,2);
INSERT INTO table2 VALUES(4,2);
CREATE TABLE table3(join_id INTEGER, x NUMERIC);
INSERT INTO table3 VALUES(4,5);
INSERT INTO table3 VALUES(1,6);
INSERT INTO table3 VALUES(3,5);
INSERT INTO table3 VALUES(2,2);
INSERT INTO table3 VALUES(2,2);
INSERT INTO table3 VALUES(1,2);
CREATE INDEX table2_join_id_idx ON table2(join_id);
CREATE INDEX table3_join_id_idx ON table3(join_id);

also seems to work, how would you handle null values? All null values in t1, t2 and t3 need to be considered as having a value of 0 — user32882, Apr 22 '19 at 11:35
@user32882 If you have to deal with null values, explicitly mentioning that in your question would be nice, as well some sample rows with them... will update, though. — Shawn, Apr 22 '19 at 11:39

SQL: simple summation query with join to 2 other tables

2 Answers2