CODEWITHSUNDEEP
mysqlsql
birubisht
birubisht

Reputation: 908

Adding two columns from tables having same structure

I have two tables as follows :-

           Table 1                      Table 2 
         date     value             date     value 
         1120101   v11              1120102   v21
         1120202   v12              1120303   v22  
         1120203   v13              1120104   v23

what is the sql query to get following output

        date         value 
       1120101       (v11)
       1120102       (v12+v21)
       1120103       (v13+v22)
       1120104       ( v23)

I tired following query but failed to get desired output 

select table1.date,
       table2.date,
       table1.delta+table2.delta as delta 
from table1,
     table2   
where table1.date=table2.date;

thanks in advance .

Upvotes: 1

Views: 113

Answers (2)

silly
silly

Reputation: 7887

play with subqueries and union:

SELECT
    c.date,
    COALESCE(a.value, 0) + COALESCE(z.value,0)
FROM (
    SELECT
        date
    FROM table1
    UNION
        SELECT date
    FROM table2
) AS c
LEFT OUTER JOIN table1 a
    ON a.date = c.date
LEFT OUTER JOIN table2 z
    ON z.date = c.date

Upvotes: 2

John Woo
John Woo

Reputation: 263743

SELECT date, SUM(value)
FROM
(
    SELECT date, value FROM table1
    UNION ALL
    SELECT date, value FROM table2
) a
GROUP BY date

Upvotes: 6

Related Questions