Edward Garcia
Edward Garcia

Reputation: 594

How to add the sum of the 'sum of the two tables?'

I created Tables T1 and T2. I managed to add their sum, but I can't seem to add the sum of the T1 and T2 together (10+12 = 22) by adding a sum() in the beginning of the code.

    CREATE TABLE T1(kW int)
    CREATE TABLE T2(kW int) 

    SELECT T1C1, T2C1
    FROM
     ( select SUM(Kw) T1C1 FROM  T1 ) A
    CROSS JOIN 
     ( select SUM(Kw) T2C1 FROM  T2 ) B

    BEGIN
    INSERT INTO T1 VALUES ('4');
    INSERT INTO T1 VALUES ('1');
    INSERT INTO T1 VALUES ('5');
    INSERT INTO T2 VALUES ('7');
    INSERT INTO T2 VALUES ('2');
    INSERT INTO T2 VALUES ('3');
    END

Upvotes: 0

Views: 36

Answers (3)

Alex Poole
Alex Poole

Reputation: 191245

Another approach is to add the results of the two subqueries directly, using the built-in dummy table dual as the main driving table:

select ( select SUM(Kw) FROM T1 )
     + ( select SUM(Kw) FROM T2 ) as total
from dual;

     TOTAL
----------
        22

Upvotes: 0

Luis Teijon
Luis Teijon

Reputation: 4899

Try using a stored procedure. Doing so you will be able to store the sum of each table on a separated variable and then return the SUM of those two variables.

You can also make a UNION ALL and SUM the column you want. Notice that you should a UNION ALL to avoid eliminating duplicated values.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311143

You should use union all to create a "virtual" column from the columns in the two tables:

SELECT SUM(kw)
FROM   (SELECT kw FROM t1
        UNION ALL
        SELECT kw FROM t2) t

Upvotes: 1

Related Questions