Inserting Sum of 2 tables into a row

Question

Hi All

I would like to store in a row of a 'Totals' table the sum calculated from 2 other tables.

I want to use a column for each table.

I tried the following:

INSERT INTO Totals
(
    Date,
    FirstTableSum,
    SecondTableSum
)

SELECT 
    '2010/01/31',
    SUM( t.Data1 ),
    SUM( v.Data2 )
FROM
    FirstTable as t,
    SecondTable as v
WHERE 
    t.Date = '2010/01/31'
AND v.Date = '2010/01/31'

If I make a query to check the sums of the 2 distinct tables I have different values.

SELECT SUM(Data1) FROM FirstTable WHERE Date='2010/01/31'
/*The result is different from FirstTableSum: Why?*/

SELECT SUM(Data2) FROM SecondTable WHERE Date='2010/01/31'
/*The result is different from SecondTableSum Why?*/

What am I doing wrong?

Thanks

Answer 1

try to write the query using a union statement. SELECT '2010/01/31', SUM( t.Data1 ) from FirstTable as t

union

SELECT '2010/01/31', SUM( t.Data1 ) from SecondTable as t

Answer 2

try this

SELECT 
 (SELECT SUM(Data1) FROM FirstTable WHERE Date=@date) FirstTableSum,
 (SELECT SUM(Data2) FROM SecondTable WHERE Date=@date) SecondTableSum

Answer 3

You are making a JOIN between the two table in your INSERT statement, the sum will be wrong since number of records is (FirstTable number of rows * SecondTable number of rows)

You either JOIN the tables with a meaningful condition, or if they are not join-able then consider SELECT with two INNER SELECTS, like zapping answer