Merge two tables with same column names, add counters

Question

I have two tables with the same columns, the first column is the name and the second is a count. I would like to merge these tables, so that each name appears with the added count of the two tables:

Table1:           Table2:            Result Table:
NAME   COUNT      NAME   COUNT       NAME   COUNT
name1  1          name3  3           name1  1
name2  2          name4  4           name2  2
name3  3          name5  5           name3  6
name4  4          name6  6           name4  8
                                     name5  5
                                     name6  6

As of the moment I have created a pretty ugly structure to execute this, and would like to know if it is possible to get the results in a more elegant way.

What I have so far (Table1 is test1 and Table2 is test2):

create table test1 ( name varchar(40), count integer);
create table test2 ( name varchar(40), count integer);
create table test3 ( name varchar(40), count integer);
create table test4 ( name varchar(40), count integer);
create table test5 ( name varchar(40), count integer);

insert into test4 (name, count) select *  from test1;
insert into test4 (name, count) select *  from test2;
insert into test3 (name , count) select t1.name, t1.count + t2.count 
from test1 t1 inner join test2 t2 on t1.name = t2.name;
select merge_db(name, count) from test3;
insert into test5 (name, count) (select name, max(count) from test4 group by name);


CREATE FUNCTION merge_db(key varchar(40), data integer) RETURNS VOID AS
    $$ -- souce: http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql
    BEGIN
        LOOP
            -- first try to update the key
            UPDATE test4 SET count = data WHERE name = key;
            IF found THEN
                RETURN;
            END IF;-- not there, so try to insert the key -- if someone else inserts the same key concurrently,        -- we could get a unique-key failure
            BEGIN
                INSERT INTO test4(name,count) VALUES (key, data);
                RETURN;
            EXCEPTION WHEN unique_violation THEN-- do nothing, and loop to try the UPDATE again
            END;
        END LOOP;
    END;
    $$
    LANGUAGE plpgsql;

score 16 · Accepted Answer · edited Sep 18 '16 at 11:06

16

=> create table t1 (name text,cnt int);
=> create table t2 (name text,cnt int);
=> insert into t1 values  ('name1',1), ('name2',2), ('name3',3), ('name4',4);
=> insert into t2 values  ('name3',3), ('name4',4), ('name5',5), ('name6',6);
=> 

select name,sum(cnt) from 
(select * from t1 
union all 
select * from t2 ) X 
group by name 
order by 1;

 name  | sum 
-------+-----
 name1 |   1
 name2 |   2
 name3 |   6
 name4 |   8
 name5 |   5
 name6 |   6
(6 rows)

edited Sep 18 '16 at 11:06

dhS

3,739
5
26
55

answered Mar 02 '11 at 04:54

Reece

7,616
4
30
46

1

thanks, just trying to figure out what the "X" is? cant find it in the documentation. – evgeni Mar 03 '11 at 19:17
2

X is a table alias (Google for that). In this case, the table is actually a table expression formed from a subquery. PostgreSQL and MySQL (at least) require that subqueries are named. The statement could have been written as 'select X.name,sum(X.cnt) ...'. However, since I don't use the alias, I just give it a dummy name. – Reece Mar 07 '11 at 00:05

Shay Rojansky · Answer 2 · 2011-03-02T02:25:52.303

9

How about this, in pure SQL:

SELECT
  COALESCE(t1.name, t2.name),
  COALESCE(t1.count, 0) + COALESCE(t2.count, 0) AS count
FROM t1 FULL OUTER JOIN t2 ON t1.name=t2.name;

Basically we're doing a full outer join on the name field to merge the two tables. The tricky part is that with the full outer join, rows that exist in one table but not the other will appear, but will have NULL in the other table; so if t1 has "name1" but t2 doesn't, the join will give us NULLs for t2.name and t2.name.

The COALESCE function returns the first non-NULL argument, so we use it to "convert" the NULL counts to 0 and to pick the name from the correct table. Thanks for the tip on this Wayne!

Good luck!

edited Mar 02 '11 at 02:25

answered Mar 02 '11 at 02:01

Shay Rojansky

15,357
2
40
69

2

Is the coalesce function what you're thinking of? e.g., `coalesce(t1.name, t2.name)`, `coalesce(t1.count, 0) + coalesce(t2.count, 0)`. – Wayne Conrad Mar 02 '11 at 02:17
5

Also, in Postgresql you can write the join condition as simply `USING (name)` and then simply refer to `name` in the column list: `SELECT name, ... FROM t1 FULL OUTER JOIN t2 USING (name)` – araqnid Mar 02 '11 at 10:56

score 0 · Answer 3 · answered Feb 19 '14 at 14:27

An alternative method is to use the NATURAL FULL OUTER JOIN combined with SUM(count) and GROUP BY name statements. The following SQL code exactly yields the desired result:

SELECT name, SUM(count) AS count FROM
  ( SELECT 1 AS tableid, * FROM t1 ) AS table1 
NATURAL FULL OUTER JOIN
  ( SELECT 2 AS tableid, * FROM t2 ) AS table2
GROUP BY name ORDER BY name

The artificial tableid column ensures that the NATURAL FULL OUTER JOIN creates a separate row for each row in t1 and for each row in t2. In other words, the rows "name3, 3" and "name4, 4" appear twice in the intermediate result. In order to merge these duplicate rows and to sum the counts we can group the rows by the name column and sum the count column.

Merge two tables with same column names, add counters

3 Answers3