Take the 2-minute tour ×

I'm executing sql queries in oracle 10g.

I want to join 3 tables into a single new table containing all the records from the 3 individual tables. The balance should be Summed up wherever the reg_id is duplicated between the 3 tables, such that there is just one entry per reg_id with the summed balance in my new table.

Sample data ( similar tables, with different values ) . tables : temp1, temp2, temp3

reg_id    |            balance
92603013               183.77
92611902               419.46
92766121               77.04
93527720               24.84
93581368               120.09
93684684                89.88
93527720               113.66

Appreciate if someone can help me with the syntax.

share|improve this question
Can there be same reg_id values in a single table ? Can there be some reg_ids that are present in one table but not in the other two ? –  Rahul Oct 28 '09 at 5:21
add comment

2 Answers

Try the following...

INSERT INTO target_table (reg_id, balance)
SELECT reg_id, sum(balance) as balance
FROM (select reg_id, balance from temp1
      select reg_id, balance from temp2
      select reg_id, balance from temp3)
GROUP BY reg_id;

I haven't tried it so don't know if the syntax is correct and whether it'll will horribly mangle your data. :)

EDIT: Changed UNION to UNION ALL. Thanks, APC!

EDIT 2: Specified the columns explicitly per Tony's recommendation.

share|improve this answer
This is basically the right idea except you should use UNION ALL not UNION. If the same REG_ID just happens to appear in two tables with the same value for BALANCE it should be counted twice, UNION filters out duplicates. –  APC Oct 28 '09 at 5:32
+1 But the "select *" syntax will only work if all 3 tables have identical columns; "select reg_id, balance" would be safer. –  Tony Andrews Oct 28 '09 at 10:26
+1 Good answer! Short and efficient! –  Christian13467 Oct 29 '09 at 14:33
add comment

I'd suggest:

SELECT coalesce(t1.reg_id, t2.reg_id, t3.reg_id) AS the_reg_id,
       coalesce(t1.balance, 0.0) + 
       coalesce(t2.balance, 0.0) +
       coalesce(t3.balance, 0.0) AS the_balance
FROM t1 FULL OUTER JOIN t2 ON (t1.reg_id = t2.reg_id)
        FULL OUTER JOIN t3 ON (t1.reg_id = t3.reg_id)
share|improve this answer
add comment

Your Answer


