|
|
Posted by Hugo Kornelis on 05/03/07 22:19
On 3 May 2007 04:10:19 -0700, rich wrote:
(snip)
>I have 2 tables 'A_Totals' & 'B_Totals' each table is identical in
>construction, each column is labeled Date,Total1,Total2,Total3 up to
>Total 18.
Hi Rich,
First, if you have two tables with the same schema, it's quite probable
that you should actually have one table with one extra distinguishing
column. I.e., instead of the two tables "Men (SSN, HairColor, ShoeSize,
Birthday)" and "Women (SSN, HairColor, ShoeSize, Birthday)", you'd want
one table "People (SSN, Sex, HairColor, ShoeSize, Birthday)".
Second, if a table has columns named XXX1, XXX2, ..., XXX18, it's quite
probable that you should actually have a seperate table for the XXX's,
with an extra column for the 1, 2, ..., 18. I.e., instead of the single
table "StoreSales (StoreID, Manager, SalesMonth1, SalesMonth2, ...,
SalesMonth12)", you'd want two tables "Stores (StoreID, Manager)" and
"Sales (StoreID, MonthNumber, Sales)".
(snip)
>I would like to create just 1 SQL statement to return both totals so
>I
>tried
>SELECT sum(A_Totals.total3),sum(B_Totals.total3) FROM
>A_Totals,B_Totals
>but this returns 1710832.00 ,1961532.00 and I can't figure out
>why :o(
That's because you specified a carthesian join between the two tables
(i.e., match every row in table 1 against every row in table 2).
Unless there is some way to match every row from one table to exactly
one row from the other table, you can't use a join for this. You COULD
use
SELECT (SELECT SUM(total3) FROM A_Totals)
+ (SELECT SUM(total3) FROM B_Totals);
But you'd probably be better off revising your design.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Navigation:
[Reply to this message]
|