7H3 IN5ID3R 7H3 IN5ID3R - 3 months ago 8
MySQL Question

Summing all 5 table values to form new table?

I have 5 tables with following properties,

+--------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| actor_id | int(11) | YES | MUL | NULL | |
| activity_object_id | int(11) | YES | MUL | NULL | |
| interest_level | tinyint(4) | YES | | 10 | |
| feed_view | smallint(6) | YES | | 0 | |
| quick_view | smallint(6) | YES | | 0 | |
| page_view | smallint(6) | YES | | 0 | |
| fullscreen_view | smallint(6) | YES | | 0 | |
| reserved1 | int(11) | YES | | NULL | |
| reserved2 | int(11) | YES | | NULL | |
| reserved3 | int(11) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
+--------------------+-------------+------+-----+---------+-------+


How we can create new temporary table which is sum of all the 5 table values. activity_object_id is unique and one table might contain activity_object_id while the other one may not.

table1 has one active_object_id say 'gowthamkey', table2 has same key 'gowthamkey', and table3 might not have 'gowthamkey'. So I want to sum up all the tables values into new table, so that it has one key 'gowthamkey' where values are sum of feed_view,quick_view,page_view,fullscreen_view,reserved1,reserved2,reserved3 except actor_id, interest_level, created_at, updated_at.

Answer

Start with a UNION, then use SUM() to add all the values from all the tables for the same activity_object_id.

CREATE TABLE new_table
AS SELECT activity_object_id, SUM(feed_view) AS feed_view, SUM(quick_view) AS quick_view, ...
    FROM (SELECT * FROM table1
          UNION ALL
          SELECT * FROM table2
          UNION ALL
          SELECT * FROM table3
          ...) AS x
    GROUP BY activity_object_id