471,108 Members | 1,616 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

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

May 3 '07 #1
2 2021
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.
>I'm new to SQL and am having trouble figuring this one out. <<
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.
>I'm new to SQL and am having trouble figuring this one out. <<
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.

>
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

May 3 '07 #2
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
May 3 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Mohammed Mazid | last post: by
3 posts views Thread by Stevey | last post: by
10 posts views Thread by glenn | last post: by
53 posts views Thread by Jeff | last post: by
56 posts views Thread by spibou | last post: by
2 posts views Thread by Allan Ebdrup | last post: by
3 posts views Thread by Zhang Weiwu | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.