# Tutorial :How to add 2 temporary tables together ### Question:

If I am creating temporary tables, that have 2 columns. `id` and `score`. I want to to add them together.

The way I want to add them is if they each contain the same `id` then I do not want to duplicate the `id` but instead add the scores together.

if I have 2 temp tables called `t1` and `t2`

and `t1` had:

``id 3 score 4  id 6 score 7  ``

and `t2` had:

``id 3 score 5  id 5 score 2  ``

I would end up with a new temp table containing:

``id 3 score 9  id 5 score 2  id 6 score 7  ``

The reason I want to do this is, I am trying to build a product search. I have a few algorithms I want to use, 1 using fulltext another not. And I want to use both algorithms so I want to create a temporary table based on algorithm1 and a temp table based on algorithm2. Then combine them.

### Solution:1

``SELECT id, SUM(score) AS score FROM (    SELECT id, score FROM t1    UNION ALL    SELECT id, score FROM t2  ) t3  GROUP BY id  ``

### Solution:2

This is untested but you should be able to perform a union on the two tables and then perform a select on the results, grouping the fields and adding the scores

``SELECT id,SUM(score) FROM  (      SELECT id,score FROM t1      UNION ALL      SELECT id,score FROM t2  ) joined  GROUP BY id  ``

### Solution:3

Perform a full outer join on the ID. Select on the ID and the sum of the two "score" columns after coalescing the values to 0.

### Solution:4

``SELECT id, SUM(score) FROM  (      SELECT id, score FROM #t1      UNION ALL      SELECT id, score FROM #t2  ) AS Temp  GROUP BY id  ``

### Solution:5

``select id, sum(score)  from (   select * from table 1   union all   select * from table2  ) tables  group by id  ``

### Solution:6

You need to create an union of those two tables then You can easily group the results.

``SELECT id, sum(score) FROM     (   SELECT id, score FROM t1   UNION    SELECT id, score FROM t2  ) as tmp  GROUP BY id;  ``

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »