Sum question

Hi

I'm new to SQL and am having trouble figuring this one out. Any help
would be very much appreciated.


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.


I have written some sctipts that populates these tables everyday.


To get a total e.g of Total3 I have used the following SQL statement


For A_Totals:
SELECT sum(total3) FROM A_Totals
This returns 18596


For B_Totals
SELECT sum(total3) FROM B_Totals
This returns 21794


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(


i also would like to create a statement that gives me a combined
total
e.g.
SELECT sum(A_Totals.total3) + sum(B_Totals.total3) FROM
A_Totals,B_Totals
this is returning 3672364.00


Thanks in advance


Rich

re: Sum question

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Your problem is that your schema is a mess. Get any book on RDBMS and
learn about Normalization. You have done some attribute splitting;
two tables with the same structure should be one table. You are
probably mimicking a file system.

CREATE TABLE Foobar
(posting_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
something_nbr INTEGER NOT NULL
CHECK (something_nbr BETWEEN 1 AND 18), --repeated group
something_amt INTEGER NOT NULL,
something_type CHAR(1) CHAR(1) NOT NULL
CHECK something_type IN ('A', 'B')), -- split attribute!
PRIMARY KEY (posting_date, something_nbr, something_type));

This will get you totals for the 1 to 18 categories or whatever they
are:

SELECT posting_date,
SUM(CASE WHEN something_nbr = 1
THEN something_amt ELSE 0 END) AS
tot_01,
..
SUM(CASE WHEN something_nbr = 18
THEN something_amt ELSE 0 END) AS
tot_18
FROM Foobar
GROUP BY posting_date;

you do not let us see it.
Your problem is that your schema is a mess. Get any book on RDBMS and
learn about Normalization. You have done some attribute splitting;
two tables with the same structure should be one table. You are
probably mimicking a file system.

CREATE TABLE Foobar
(posting_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
something_nbr INTEGER NOT NULL
CHECK (something_nbr BETWEEN 1 AND 18), --repeated group
something_amt INTEGER NOT NULL,
something_type CHAR(1) CHAR(1) NOT NULL
CHECK something_type IN ('A', 'B')), -- split attribute!
PRIMARY KEY (posting_date, something_nbr, something_type));

This will get you totals for the 'A' and 'B' types

SELECT posting_date,
SUM(CASE WHEN something_type = 'A;
THEN something_amt ELSE 0 END) AS
tot_A,
SUM(CASE WHEN something_type = 'B'
THEN something_amt ELSE 0 END) AS
tot_B
FROM Foobar
GROUP BY posting_date;

You can combine and them as you wish into another SELECT.

re: Sum question

On 3 May 2007 04:10:19 -0700, rich wrote:

(snip)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)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