February 20, 2009 at 12:50 pm
josephptran2002 (2/20/2009)
Hello Grant Fritchey,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
Thank you so much
Yeah, I saw the original post and I responded to it. So, what's the deal? Do you have tables scattered in more than one database on purpose?
"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 20, 2009 at 1:12 pm
Hello Grant Fritchey,
Below are the codes that I wrote but when it displays the report it wasn't display the total number at the bottom of each column. So that why I ask? To be honest, I am new to SQL so I don't know how to do it that why I ask. If the way I ask you guys are not polited, please I am apoloziged.
SELECT dbo.BOCLEARED.BOCLEARED, dbo.CTCLEARED.CTCLEARED, dbo.MECLEARED.MECLEARED, dbo.NHCLEARED.NHCLEARED,
dbo.RICLEARED.RICLEARED, dbo.WOCLEARED.WOCLEARED, dbo.VTCLEARED.VTCLEARED, dbo.ALLCASESCLEARED.TOTALCLEARED,
dbo.ALLCASESCLEARED.CLEAREDDATE, dbo.ALLCASESCLEARED.DDS
FROM dbo.BOCLEARED FULL OUTER JOIN
dbo.ALLCASESCLEARED ON dbo.BOCLEARED.BOCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.CTCLEARED ON dbo.CTCLEARED.CTCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.MECLEARED ON dbo.MECLEARED.MECLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.NHCLEARED ON dbo.NHCLEARED.NHCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.RICLEARED ON dbo.RICLEARED.RICLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.WOCLEARED ON dbo.WOCLEARED.WOCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.VTCLEARED ON dbo.VTCLEARED.VTCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
February 20, 2009 at 1:27 pm
Try this:
SELECT
dbo.BOCLEARED.BOCLEARED,
dbo.CTCLEARED.CTCLEARED,
dbo.MECLEARED.MECLEARED,
dbo.NHCLEARED.NHCLEARED,
dbo.RICLEARED.RICLEARED,
dbo.WOCLEARED.WOCLEARED,
dbo.VTCLEARED.VTCLEARED,
dbo.ALLCASESCLEARED.TOTALCLEARED,
dbo.ALLCASESCLEARED.CLEAREDDATE,
dbo.ALLCASESCLEARED.DDS
FROM
dbo.BOCLEARED
FULL OUTER JOIN dbo.ALLCASESCLEARED
ON dbo.BOCLEARED.BOCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
FULL OUTER JOIN dbo.CTCLEARED
ON dbo.CTCLEARED.CTCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
FULL OUTER JOIN dbo.MECLEARED
ON dbo.MECLEARED.MECLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
FULL OUTER JOIN dbo.NHCLEARED
ON dbo.NHCLEARED.NHCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
FULL OUTER JOIN dbo.RICLEARED
ON dbo.RICLEARED.RICLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
FULL OUTER JOIN dbo.WOCLEARED
ON dbo.WOCLEARED.WOCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
FULL OUTER JOIN dbo.VTCLEARED
ON dbo.VTCLEARED.VTCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
union all
SELECT
sum(dbo.BOCLEARED.BOCLEARED),
sum(dbo.CTCLEARED.CTCLEARED),
sum(dbo.MECLEARED.MECLEARED),
sum(dbo.NHCLEARED.NHCLEARED),
sum(dbo.RICLEARED.RICLEARED),
sum(dbo.WOCLEARED.WOCLEARED),
sum(dbo.VTCLEARED.VTCLEARED),
sum(dbo.ALLCASESCLEARED.TOTALCLEARED),
sum(dbo.ALLCASESCLEARED.CLEAREDDATE),
sum(dbo.ALLCASESCLEARED.DDS)
FROM
dbo.BOCLEARED
FULL OUTER JOIN dbo.ALLCASESCLEARED
ON dbo.BOCLEARED.BOCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
FULL OUTER JOIN dbo.CTCLEARED
ON dbo.CTCLEARED.CTCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
FULL OUTER JOIN dbo.MECLEARED
ON dbo.MECLEARED.MECLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
FULL OUTER JOIN dbo.NHCLEARED
ON dbo.NHCLEARED.NHCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
FULL OUTER JOIN dbo.RICLEARED
ON dbo.RICLEARED.RICLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
FULL OUTER JOIN dbo.WOCLEARED
ON dbo.WOCLEARED.WOCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
FULL OUTER JOIN dbo.VTCLEARED
ON dbo.VTCLEARED.VTCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 20, 2009 at 1:35 pm
Hello GSquared,
THank you very much for your helps...I'm greatly appreciated your helps.
Again, Thank you so much
Very Respectful
February 20, 2009 at 1:48 pm
Hello GSquared,
I typed exactly what you suggest me to do then it gives me a message:
"Column dbo.ALLCASESCLEARED.CLEAREDDATE is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"
and
it changes the code to different codes after I click "Execute"
SELECT dbo.BOCLEARED.BOCLEARED, dbo.CTCLEARED.CTCLEARED, dbo.MECLEARED.MECLEARED, dbo.NHCLEARED.NHCLEARED,
dbo.RICLEARED.RICLEARED, dbo.WOCLEARED.WOCLEARED, dbo.VTCLEARED.VTCLEARED, dbo.ALLCASESCLEARED.TOTALCLEARED,
dbo.ALLCASESCLEARED.CLEAREDDATE, dbo.ALLCASESCLEARED.DDS
FROM dbo.BOCLEARED FULL OUTER JOIN
dbo.ALLCASESCLEARED ON dbo.BOCLEARED.BOCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.CTCLEARED ON dbo.CTCLEARED.CTCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.MECLEARED ON dbo.MECLEARED.MECLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.NHCLEARED ON dbo.NHCLEARED.NHCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.RICLEARED ON dbo.RICLEARED.RICLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.WOCLEARED ON dbo.WOCLEARED.WOCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.VTCLEARED ON dbo.VTCLEARED.VTCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
UNION ALL
SELECT SUM(BOCLEARED_1.BOCLEARED) AS BOCLEARED, SUM(CTCLEARED_1.CTCLEARED) AS CTCLEARED, SUM(MECLEARED_1.MECLEARED)
AS MECLEARED, SUM(NHCLEARED_1.NHCLEARED) AS NHCLEARED, SUM(RICLEARED_1.RICLEARED) AS RICLEARED,
SUM(WOCLEARED_1.WOCLEARED) AS WOCLEARED, SUM(VTCLEARED_1.VTCLEARED) AS VTCLEARED, SUM(ALLCASESCLEARED_1.TOTALCLEARED)
AS TOTALCLEARED, ALLCASESCLEARED_1.CLEAREDDATE, ALLCASESCLEARED_1.DDS
FROM dbo.BOCLEARED AS BOCLEARED_1 FULL OUTER JOIN
dbo.ALLCASESCLEARED AS ALLCASESCLEARED_1 ON BOCLEARED_1.BOCLEAREDDATE = ALLCASESCLEARED_1.CLEAREDDATE FULL OUTER JOIN
dbo.CTCLEARED AS CTCLEARED_1 ON CTCLEARED_1.CTCLEAREDDATE = ALLCASESCLEARED_1.CLEAREDDATE FULL OUTER JOIN
dbo.MECLEARED AS MECLEARED_1 ON MECLEARED_1.MECLEAREDDATE = ALLCASESCLEARED_1.CLEAREDDATE FULL OUTER JOIN
dbo.NHCLEARED AS NHCLEARED_1 ON NHCLEARED_1.NHCLEAREDDATE = ALLCASESCLEARED_1.CLEAREDDATE FULL OUTER JOIN
dbo.RICLEARED AS RICLEARED_1 ON RICLEARED_1.RICLEAREDDATE = ALLCASESCLEARED_1.CLEAREDDATE FULL OUTER JOIN
dbo.WOCLEARED AS WOCLEARED_1 ON WOCLEARED_1.WOCLEAREDDATE = ALLCASESCLEARED_1.CLEAREDDATE FULL OUTER JOIN
dbo.VTCLEARED AS VTCLEARED_1 ON VTCLEARED_1.VTCLEAREDDATE = ALLCASESCLEARED_1.CLEAREDDATE
February 20, 2009 at 1:58 pm
Hello GSquared,
This is what I do to have the total at the bottom of each column. But I don't know, Is this a right way to do? If it is then how can I merge to summary report? so it can show the total at the bottom of each column.
First, I create a brand new table and that table has the same field name with the summary report. But it shows the total for each column name.
Use [Regional Office Claims Adjudication Process-Disability (ROCAP)];
If Object_ID('GRANDTOTAL') IS NOT NULL DROP TABLE GRANDTOTAL;
CREATE TABLE GRANDTOTAL (BOCLEARED INT, CTCLEARED INT, MECLEARED INT, NHCLEARED INT, RICLEARED INT, WOCLEARED INT, VTCLEARED INT, TOTALCLEARED INT);
Second, insert the result Total into each column
Insert into GRANDTOTAL
Select
(Select sum(BOCLEARED) from dbo.BOCLEARED),
(Select sum(CTCLEARED) from dbo.CTCLEARED),
(Select sum(MECLEARED) from dbo.MECLEARED),
(Select sum(NHCLEARED) from dbo.NHCLEARED),
(Select sum(RICLEARED) from dbo.RICLEARED),
(Select sum(WOCLEARED) from dbo.WOCLEARED),
(Select sum(VTCLEARED) from dbo.VTCLEARED),
(Select sum(TOTALCLEARED) from dbo.ALLCASESCLEARED)
Third, how can I merge to this report below? I have no idea, do you?
SELECT dbo.BOCLEARED.BOCLEARED, dbo.CTCLEARED.CTCLEARED, dbo.MECLEARED.MECLEARED, dbo.NHCLEARED.NHCLEARED,
dbo.RICLEARED.RICLEARED, dbo.WOCLEARED.WOCLEARED, dbo.VTCLEARED.VTCLEARED, dbo.ALLCASESCLEARED.TOTALCLEARED,
dbo.ALLCASESCLEARED.CLEAREDDATE, dbo.ALLCASESCLEARED.DDS
FROM dbo.BOCLEARED FULL OUTER JOIN
dbo.ALLCASESCLEARED ON dbo.BOCLEARED.BOCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.CTCLEARED ON dbo.CTCLEARED.CTCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.MECLEARED ON dbo.MECLEARED.MECLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.NHCLEARED ON dbo.NHCLEARED.NHCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.RICLEARED ON dbo.RICLEARED.RICLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.WOCLEARED ON dbo.WOCLEARED.WOCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE FULL OUTER JOIN
dbo.VTCLEARED ON dbo.VTCLEARED.VTCLEAREDDATE = dbo.ALLCASESCLEARED.CLEAREDDATE
Thank you so much
February 20, 2009 at 2:37 pm
Honestly, I don't know why the query needs to have the totals in it. Usually, that's something that you do in the web page or other application, not in the query.
The code being rewritten is something you can avoid if you don't use "Design View", but instead just create an editor window by clicking "New Query".
The error message is because either you or your computer removed "sum" from around that column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 20, 2009 at 2:42 pm
Hello GSquared,
Thank you very much for your efforts to help me solve the problems.
So if I use new query then I can avoid the codes are changing to some thing else right????
Thank you
February 20, 2009 at 2:45 pm
Yes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 20, 2009 at 2:48 pm
Hello GSquared,
If I run the code in New query like you told me in order to avoid the codes are changing to something else. So if I run in New Query then how can I save the the outlook in VIEWS? The reason I want to save it because I wish to display the report on the front end.
Thank you GSquared
February 20, 2009 at 2:55 pm
Hello GSquared,
Thank you for your lessons, wish you and your family have a wonderful weekend.
again, Thank you very much
February 20, 2009 at 2:57 pm
Type the words "Create View" and the name of the view, at the top of it. Then add "AS" between the name of the view and the first place that it says "select".
Like this:
create view MyView
as
select
When you execute that, it will create the view.
If you get an error that says that it already exists, change the word "create" to "alter".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply