Fussion of two SQlite3 tables into new one with sum of value in a column

Question

I have two SQLite3 tables in two separate sqlite files:

Table1 (in file1):

Id    Number       
----- ------
21    1 
22    2
23    3 
24    4

and Table2 (in file2):

Id    Number       
----- ------
21    15 
32    16
33    17 
34    18

I would like to produce a new table, which accumulates values of Number if there is match. So I would like an output:

TableSummary (should be in Newfile or in file1):

Id    Number       
----- ------
21    16
22    2
23    3 
24    4 
32    16
33    17 
34    18

What kind of statement I should use to achieve the result?

I forgot to mention that those tables are in two separate files — Ivaylo, Apr 29 '14 at 13:59

score 2 · Accepted Answer · answered Apr 29 '14 at 13:47

2

First, use UNION ALL to combine both tables:

SELECT Id, Number FROM Table1
UNION ALL
SELECT Id, Number FROM Table2

Then combine the duplicates by using GROUP BY:

SELECT Id, SUM(Number)
FROM (SELECT Id, Number FROM Table1
      UNION ALL
      SELECT Id, Number FROM Table2)
GROUP BY Id

answered Apr 29 '14 at 13:47

CL.

How to do it if the tables are in different files? The result table should be in one of the files or in a new one. – Ivaylo Apr 29 '14 at 14:07

1 Answers1