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
try this
SELECT
(SELECT SUM(Data1) FROM FirstTable WHERE Date=@date) FirstTableSum,
(SELECT SUM(Data2) FROM SecondTable WHERE Date=@date) SecondTableSum
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
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
精彩评论