CODEWITHSUNDEEP
sqladdition
Verhogen
Verhogen

Reputation: 28611

Merge and add values from two tables

Is it possible to craft a query that adds values within two tables:

For example, say you have two tables

id    value
--    -----
a       1
c       2
d       3
f       4
g       5

and 

id     value
--     -----
a        1
b        2
c        3
d        4
e        5

Then when you 'add' the two tables you would get the result where the id's match. So, a=1+1=2, and simply the same result where they don't. So the query would return:

id     value
--     -----
a       2
b       2
c       5
d       7
e       5
f       4
g       5

Upvotes: 2

Views: 263

Answers (5)

TerrorAustralis
TerrorAustralis

Reputation: 2923

SELECT ISNULL(T1.Col1.T2.Col1) as ID, (ISNULL(T1.Col2,0) + ISNULL(T2.Col2,0)) as SumCols
FROM T1 OUTER JOIN T2 ON T1.Col1 = T2.Col2

No grouping or anything. It handles the following cases

if an id is in T1 but not in T2 you will get the value in T1 and vice versa. This handles bi-directional inclusion.

If an ID is in both you will get the sum

Upvotes: 0

gbn
gbn

Reputation: 432180

SELECT
    COALESCE(t1.id, t2.id) AS id,
    COALESCE(t1.value, 0) + COALESCE(t2.value, 0) AS value
FROM
    t1
    FULL OUTER JOIN
    t2 ON t1.id = t2.id

OR

SELECT
    foo.id,
    COALESCE(t1.value, 0) + COALESCE(t2.value, 0) AS value
FROM
    (
    SELECT t1.id FROM t1
    UNION
    SELECT t2.id FROM t2
    ) foo
    LEFT JOIN
    t1 ON foo.id = t1.id
    LEFT JOIN
    t2 ON foo.id = t2.id

Upvotes: 0

Jonathan Leffler
Jonathan Leffler

Reputation: 753475

You could do it like this - but the other answers are probably swifter:

SELECT t1.id, t1.value + t2.value AS value
  FROM t1 INNER JOIN t2 ON t1.id = t2.id
UNION
SELECT t1.id, t1.value
  FROM t1
 WHERE t1.id NOT IN (SELECT t2.id FROM t2)
UNION
SELECT t2.id, t2.value
  FROM t2
 WHERE t2.id NOT IN (SELECT t1.id FROM t1)

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332521

Use:

  SELECT x.id,
         SUM(x.value)
    FROM (SELECT t.id,
                 t.value
            FROM TABLE_1 t
          UNION ALL
          SELECT t2.id,
                 t2.value
            FROM TABLE_2 t2) x
GROUP BY x.id

Upvotes: 2

John Boker
John Boker

Reputation: 83699

maybe something like

select coalesce(t1.id, t2.id) as id, (coalesce(t1.value, 0) + coalesce(t2.value, 0)) as value 
from table1 t1 full outer join table2 t2 on t1.id = t2.id

Upvotes: 5

Related Questions