adding two columns in two tables having multiple layers

Question

I am having two tables here from which I needed to add two columns.

table 1                     table 2

1   ram     100 null        1   ram     100 1000
2   ram     200 1000        2   ram     200 null
3   ram     100 2000        3   ram     100 3000
4   ram     100 3000        4   ram     100 4000
5   ram     100 null        5   ram     100 5000
1   rahim   100 5000        1   rahim   100 null
2   ram     200 6000        2   ram     200 7000
3   ram     200 null        3   ram     200 8000
4   ram     200 null        4   ram     200 9000
5   rahim   100 9000        5   rahim   100 null
1   robert  100 10000       1   robert  100 11000
2   rahim   200 11000       2   rahim   200 12000
3   ram     300 12000       3   ram     300 null
4   rahim   400 13000       4   rahim   400 14000
5   robert  100 14000       5   robert  100 15000

result should be in the form:

1   ram     100 1000
2   ram     200 -1000
3   ram     100 1000
4   ram     100 1000
5   ram     100 5000
1   rahim   100 -5000
2   ram     200 1000
3   ram     200 8000
4   ram     200 9000
5   rahim   100 -9000
1   robert  100 1000
2   rahim   200 1000
3   ram     300 -12000
4   rahim   400 1000
5   robert  100 1000

Oh! Arithmetically adding... now that makes some kind of sense. — Siyual
– Siyual, Commented Nov 17, 2014 at 17:24

sgeddes · Accepted Answer · 2014-11-17 17:25:20Z

3

You can use join with coalesce to remove the null values:

select t1.id, t1.somefield, t1.someint, 
       coalesce(t2.someint2,0)-coalesce(t1.someint2,0)
from table1 t1
  join table2 t2 on t1.id = t2.id 
    and t1.somefield = t2.somefield
    and t1.someint = t2.someint

SQL Fiddle Demo

Based on your input data, this joins on the first 3 columns. Not completely sure this is what you want, but should get you going in the correct direction.

answered Nov 17, 2014 at 17:25

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Maheswaran Ravisankar · Accepted Answer · 2014-11-18 07:51:32Z

2

I think try subtract table2.col4 with table1.col4.

SELECT a.col1,
       a.col2,
       a.col3,
       NVL(a.col4, 0) - NVL(b.col4, 0) SUB
FROM   table1 A
       JOIN table2 B
         ON A.col1 = b.col1
            AND a.col2 = b.col2

edited Nov 18, 2014 at 7:51

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

answered Nov 17, 2014 at 17:25

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Collectives™ on Stack Overflow

adding two columns in two tables having multiple layers

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related