February 17, 2009 at 1:29 pm
Hello ALL
I have 4 tables such as BOCLEARED, CTCLEARED, NHCLEARED, and TOTALCLEARED.
In table BOCLEARED (given), it includes 3 columns such as
DDS BOCLEARED BOCLEAREDDATE
In table CTCLEARED (given), it includes 3 columns such as
DDS CTCLEARED CTCLEAREDDATE
In table NHCLEARED (given), it includes 3 columns such as
DDS NHCLEARED NHCLEARED
In table TOTALCLEARED (given), it includes 3 columns such as
DDS TOTALCLEARED CLEAREDDATE
Now, I wish to create a table which calls GRANDTOTAL and the table "GRANDTOTAL" is going to be built by taking one column of each table and transfer the data of each column such as BOCLEARED, CTCLEARED, NHCLEARED, and TOTALCLEARED. After being transfered to a brand new table calls GRANDTOTAL, I wish to calculate the sum of each column (the total at the bottom of each column).
Table GRANDTOTAL
BOCLEARED CTCLEARED NHCLEARED TOTALCLEARED
WOULD ANYONE PLEASE TELL ME HOW TO DO IN A BOLD SENTENCE? I am new to SQL 2005, Please help me
THANK YOU SO MUCH
February 17, 2009 at 4:01 pm
insert into grandtotal
select
(select sum(bocleared) from bocleared),
(select sum(ctcleared) from ctcleared),
(select sum(nhcleared) from nhcleared),
(select sum(totalcleared) from totalcleared)
I would think something along those lines would do the trick ...
BTW, I wouldn't use the same name for a field that you've already used for a table name 😉
February 19, 2009 at 6:35 am
Hello Grass,
How are you doing? Thanks for the respond and help me to understand the codes.
I have a question to ask you about your codes.
Since I don't have the table GrandTotal yet, do I have to create a table GRANDTOTAL in order to be able to store the data from other tables.
I am new to this SQL, would you please tell me how?
Thank you
February 19, 2009 at 11:23 am
You're welcome.
Yes, to use the code I gave you, you need to create a table first to Insert Into. Here's the SQL to do this:
USE InsertYourDBNameHere;
IF OBJECT_ID('GRANDTOTAL') IS NOT NULL DROP TABLE GRANDTOTAL;
CREATE TABLE GRANDTOTAL (bocleared INT, ctcleared INT, nhcleared INT, totalcleared INT);
February 19, 2009 at 11:43 am
Hello Grass,
So I have to type this code like:
USE GRANDTOTAL; --> what database name should I insert here? IS IT GRANDTOTAL? the database that I wish to transfer data from other tables.
IF OBJECT_ID('GRANDTOTAL') IS NOT NULL DROP TABLE
CREATE TABLE GRANDTOTAL (BOCLEARED INT, CTCLEARED INT, NHCLEARED INT, TOTALCLEARED INT);
Thank you Grass
February 19, 2009 at 12:01 pm
No your database name is not the table name, its the database name. Your SQL Server has a name (the name of the Server itself on your network), and then within the SQL Server install you have databases. Some of them are automatically installed and used by the Server itself, such as the database called MASTER. Your Tables, Views, etc, should all be contained within a database that was created on your SQL Server after the installation was completed, probably either by you, or someone else in the company.
If you go into MSSMS and open the object explorer, you should see your Server Name at the top, and below that, a node that says Databases. If you click the + next to databases, you should be able to see the ones that have been created on your server.
You want to USE ... the database that contains the data we're talking about here.
February 19, 2009 at 12:30 pm
Why move all the data to a new table? You can just query the existing tables. I assume the DDS column is some kind of key?
SELECT SUM(b.BOCLEARED) AS BOTotal
,SUM(c.CTCLEARED) AS CTTotal
,SUM(n.NHCLEARED) AS NHTotal
,SUM(b.BOCLEARED + c.CTCLEARED + n.NHCLEARED) AS GrandTotal
FROM dbo.BOCLEARED b
JOIN dbo.CTCLEARED c
ON b.DDS = c.DDS
JOIN dbo.NHCLEARED n
OIN c.DDS = n.DDS
You don't need to move the data from place to place in permanent storage to pull information out of it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 19, 2009 at 12:37 pm
josephptran2002 (2/17/2009)
I am new to SQL 2005, Please help me
May I suggest a good introductory SQL site?
http://www.w3schools.com/sql/sql_intro.asp
Or perhaps a good book on T-SQL fundamentals?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2009 at 12:58 pm
I don't think your codes work .....here is what I write
Select Sum(b.BOCLEARED) AS BOTotal, Sum(c.CTCLEARED) AS CTTotal, Sum(m.MECLEARED) AS METotal, Sum(n.NHCLEARED) AS NHTotal, Sum(r.RICLEARED) AS RITotal, Sum(w.WOCLEARED) AS WOTotal, Sum(v.VTCLEARED) AS VTTotal, Sum(b.BOCLEARED + c.CTCLEARED + m.MECLEARED + n.NHCLEARED + r.RICLEARED + w.WOCLEARED + v.VTCLEARED) AS GRANDTOTAL
From dbo.BOCLEARED b
Join dbo.CTCLEARED c
ON b.DDS = c.DDS
Join dbo.MECLEARED m
ON c.DDS = m.DDS
Join dbo.NHCLEARED n
ON m.DDS = n.DDS
Join dbo.RICLEARED r
ON n.DDS = r.DDS
Join dbo.WOCLEARED w
ON r.DDS = w.DDS
Join dbo.VTCLEARED v
ON w.DDS = v.DDS
DO you know why? The error message is: Invalid Column name 'DDS' but there is a column name in each table.
February 19, 2009 at 1:08 pm
Hello Grass
Why I type exactly the name appears on the Master database but it still gives me "Could not locate entry in sysdatabases for database 'Regional'. No entry found with that name. Make sure that the name is entered correctly. "
How can I copy and paste? so i can get the correct name of the master database.
Thank you Grass
February 19, 2009 at 1:09 pm
Please post the CREATE TABLE scripts for your tables.
February 19, 2009 at 1:10 pm
josephptran2002 (2/19/2009)
The error message is: Invalid Column name 'DDS' but there is a column name in each table.
Are you sure that all the tables have a column with that exact name? The error indicates that one of the tables doesn't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2009 at 1:10 pm
Hi there,
How can I copy the script of my CREATE TABLE?
THank you
P.S: I'm new to SQL, sorry I try to learn as fast as I can
February 19, 2009 at 1:12 pm
josephptran2002 (2/19/2009)
I don't think your codes work .....here is what I writeSelect Sum(b.BOCLEARED) AS BOTotal, Sum(c.CTCLEARED) AS CTTotal, Sum(m.MECLEARED) AS METotal, Sum(n.NHCLEARED) AS NHTotal, Sum(r.RICLEARED) AS RITotal, Sum(w.WOCLEARED) AS WOTotal, Sum(v.VTCLEARED) AS VTTotal, Sum(b.BOCLEARED + c.CTCLEARED + m.MECLEARED + n.NHCLEARED + r.RICLEARED + w.WOCLEARED + v.VTCLEARED) AS GRANDTOTAL
From dbo.BOCLEARED b
Join dbo.CTCLEARED c
ON b.DDS = c.DDS
Join dbo.MECLEARED m
ON c.DDS = m.DDS
Join dbo.NHCLEARED n
ON m.DDS = n.DDS
Join dbo.RICLEARED r
ON n.DDS = r.DDS
Join dbo.WOCLEARED w
ON r.DDS = w.DDS
Join dbo.VTCLEARED v
ON w.DDS = v.DDS
DO you know why? The error message is: Invalid Column name 'DDS' but there is a column name in each table.
If you are in the right database and the tables are structured as you say they are, then you should be able to run this query. The error implies that at least one of the tables doesn't have a column called DDS. It probably provided you with a line number. Minus any comments at the top of the screen, you should be able to determine which line of the code caused the problem.
From your other posts, it doesn't sound like you're connected to the database you think you're connected to.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 19, 2009 at 1:17 pm
josephptran2002 (2/19/2009)
Hi there,How can I copy the script of my CREATE TABLE?
THank you
P.S: I'm new to SQL, sorry I try to learn as fast as I can
Right click the table and select the menu item Script Table as and then the second menu item CREATE To. You can use the clipboard and paste it directly to here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply