February 20, 2009 at 7:24 am
Hello Experts,
Below is the code that I wrote, at the bottom of each column where I wish to have the sum (total) and I wish to have total of each column will add up to the sum/total of the column "TOTALCLEARED".
For example
BOCLEARE CTCLEARED NHCLEARED....TOTALCLEARED
1 2 3 6
5 1 4 10
2 7 8 17
----------------------------------------
8 10 15 33--> I wish to have
this result.
Below are the codes that I wrote but It won't display the total/sum on each column.
Would you please tell me how?
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
February 20, 2009 at 7:41 am
Why are you joining on date columns? Why do you have FULL OUTER JOINS?
It's not displaying the total because there's no logic in that query to generate a total. You've just asked for all of the columns from all of the tables. To have an aggregation, you need an aggregation funtion, like SUM.
I'll give you a hint.
SELECT col1, col2, col3 FROM SomeTable
UNION ALL
SELECT SUM(col1), SUM(col2), SUM(col3) FROM SomeTable
That said, this kind of totalling is usually better done in the client application rather than SQL.
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 20, 2009 at 7:50 am
Hello GilaMonster,
How are you doing? Thank you for your responds, the reason I used the FULL Join because I join the CLEAREDDATE of each table to CLEAREDATE of the "ALLCASESCLEARED" where it is the sum of all columns "BOCLEARED + CTCLEARED + MECLEARED + etc.". So I can have the summary report of all offices.
BOCLEARED CTCLEARED....TOTALCLEARED CLEAREDDATE DDS
1 2 3 1/1/2009 BO, CT
2 5 7 1/5/2009 BO, CT
3 3 1/7/2009 BO
The code that I send you which gives me the outlook above, but It won't give the total at the bottom of each column like I ask you.
So I have to type the codes below but for the part "From Sometable", do I have to type the table name of each column where it belongs to?
SELECT col1, col2, col3 FROM SomeTable
UNION ALL
SELECT SUM(col1), SUM(col2), SUM(col3) FROM SomeTable
Thank you
P.S: What should I do?
February 20, 2009 at 7:59 am
josephptran2002 (2/20/2009)
the reason I used the FULL Join because I join the CLEAREDDATE of each table to CLEAREDATE of the "ALLCASESCLEARED" where it is the sum of all columns "BOCLEARED + CTCLEARED + MECLEARED + etc.". So I can have the summary report of all offices.
Doesn't explain why you're joining on the dates or using a full join.
P.S: What should I do?
Buy a good beginners book on SQL, take a SQL class or look at the website that I referred to in a post yesterday.
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 20, 2009 at 8:05 am
How are you doing? Thank you for your responds, the reason I used the FULL Join because I join the CLEAREDDATE of each table to CLEAREDATE of the "ALLCASESCLEARED" where it is the sum of all columns "BOCLEARED + CTCLEARED + MECLEARED + etc.". So I can have the summary report of all offices.
BOCLEARED CTCLEARED....TOTALCLEARED CLEAREDDATE DDS
1 2 3 1/1/2009 BO, CT
2 5 7 1/5/2009 BO, CT
3 3 1/7/2009 BO
The code that I send you which gives me the outlook above, but It won't give the total at the bottom of each column like I ask you.
So I have to type the codes below but for the part "From Sometable", do I have to type the table name of each column where it belongs to?
Would you please show me how?
Here is the code for one office BOCLEARED
WITH CTE AS (SELECT COALESCE (a.DDS, b.DDS, c.DDS, d.DDS, e.DDS, f.DDS, g.DDS, h.DDS, i.DDS, j.DDS, k.DDS, l.DDS) AS DDS, ISNULL(a.DECleared, 0)
+ ISNULL(b.SomaticMCCleared, 0) + ISNULL(c.PsycMCCleared, 0) + ISNULL(d.DESecondCleared, 0)
+ ISNULL(e.SomaticMCSecondCleared, 0) + ISNULL(f.PsycMCSecondCleared, 0) + ISNULL(g.DEThirdCleared, 0)
+ ISNULL(h.SomaticMCThirdCleared, 0) + ISNULL(i.PsycMCThirdCleared, 0) + ISNULL(j.DEFourthCleared, 0)
+ ISNULL(k.SomaticMCFourthCleared, 0) + ISNULL(l.PsycMCFourthCleared, 0) AS BOCLEARED, COALESCE (a.DEClearedDate,
b.SomaticMCClearedDate, c.PsycMCClearedDate, d.DESecondClearedDate, e.SomaticMCSecondClearedDate,
f.PsycMCSecondClearedDate, g.DEThirdClearedDate, h.SomaticMCThirdClearedDate, i.PsycMCThirdClearedDate,
j.DEFourthClearedDate, k.SomaticMCFourthClearedDate, l.PsycMCFourthClearedDate) AS BOCLEAREDDATE
FROM dbo.BODECleared AS a FULL OUTER JOIN
dbo.BOSomaticMCCleared AS b ON b.SomaticMCClearedDate = a.DEClearedDate FULL OUTER JOIN
dbo.BOPsycMCCleared AS c ON c.PsycMCClearedDate = b.SomaticMCClearedDate FULL OUTER JOIN
dbo.BODESecondCleared AS d ON d.DESecondClearedDate = c.PsycMCClearedDate FULL OUTER JOIN
dbo.BOSomaticMCSecondCleared AS e ON e.SomaticMCSecondClearedDate = d.DESecondClearedDate FULL OUTER JOIN
dbo.BOPsycMCSecondCleared AS f ON f.PsycMCSecondClearedDate = e.SomaticMCSecondClearedDate FULL OUTER JOIN
dbo.BODEThirdCleared AS g ON g.DEThirdClearedDate = f.PsycMCSecondClearedDate FULL OUTER JOIN
dbo.BOSomaticMCThirdCleared AS h ON h.SomaticMCThirdClearedDate = g.DEThirdClearedDate FULL OUTER JOIN
dbo.BOPsycMCThirdCleared AS i ON i.PsycMCThirdClearedDate = h.SomaticMCThirdClearedDate FULL OUTER JOIN
dbo.BODEFourthCleared AS j ON j.DEFourthClearedDate = i.PsycMCThirdClearedDate FULL OUTER JOIN
dbo.BOSomaticMCFourthCleared AS k ON k.SomaticMCFourthClearedDate = j.DEFourthClearedDate FULL OUTER JOIN
dbo.BOPsycMCFourthCleared AS l ON l.PsycMCFourthClearedDate = k.SomaticMCFourthClearedDate)
SELECT MIN(DISTINCT DDS) AS DDS, SUM(BOCLEARED) AS BOCLEARED, BOCLEAREDDATE
FROM (SELECT DDS, ISNULL(BOCLEARED, 0) AS BOCLEARED, CONVERT(varchar(16), BOCLEAREDDATE, 101) AS BOCLEAREDDATE
FROM CTE AS CTE_1) AS DATA
WHERE (DDS = 'BO')
GROUP BY BOCLEAREDDATE WITH ROLLUP
February 20, 2009 at 1:46 pm
SQL won't easily give you the totals at the bottom of columns. Your front end would do that.
February 20, 2009 at 1:51 pm
Hello Steve Jones,
How are you doing? Thank you for your reply, how can I do it on the front end? Would you please tell me how?
Thank you Steve,
Very Respectful
April 2, 2009 at 9:10 am
sorry for the late response.
This is a simple counter (or series of counters) or variables that you add the rows to as you loop through them.
Calculating the total of a set of rows (or set of data) is a very simple programming exercise. If this is something you don't understand, I would suggest you get some basic programming training.
April 2, 2009 at 10:21 am
Hi
All the summing operations based on the amount should be part of Front-End.
One Big Reasons behind this:
1) If you have Pagination in UI you would want to display the totals on that page or for all the records which matcges the Criteria.
April 2, 2009 at 11:23 pm
Vijaya Kadiyala (4/2/2009)
HiAll the summing operations based on the amount should be part of Front-End.
One Big Reasons behind this:
1) If you have Pagination in UI you would want to display the totals on that page or for all the records which matcges the Criteria.
Heh... what makes you think that there's a front end for this problem? 😉 And what would you do if you didn't have a front end available? :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
April 3, 2009 at 8:14 am
Hey Jeff,
That is Sixth-Sense...;-)The way i see is by looking at the requirement this is a report. Either this report is comming from SSRS or any front-end application.:hehe:
In one of his replies he also mentioned "how can I do it on the front end? Would you please tell me how?" and steve gave pretty good reply.
April 3, 2009 at 8:43 am
Hello Vijaya Kadiyala
How are you doing? I try to be nice to ask for helps and you think you know the answer for it. This is not an issue at the front end problem, if you don't know how to solve it please please don't misguide people to different direction. I solved this issue by using the store procedure codes, so I can display it on the front end.
Again, thank you for all of your efforts. If you don't want to help new developer out and try to insult them, please be nice. That's all I want.
Very Respectful
Joseph Tran
P.S: Think about when you first start programming in SQL, then you can understand.
April 3, 2009 at 8:59 am
josephptran2002 (4/3/2009)
This is not an issue at the front end problem, if you don't know how to solve it please please don't misguide people to different direction. I solved this issue by using the store procedure codes, so I can display it on the front end.
He's suggesting that perhaps in this case the front end would be a better place to do this kind of data manipulation that a stored procedure. It's always an option and often formatting's better done on the front end than in the database.
If you don't want to help new developer out and try to insult them, please be nice.
No one's insulting you here. If you want to ignore his advice, go right ahead, but don't say that someone offering an alternative is insulting you.
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
April 3, 2009 at 9:07 am
Hello Gila,
Thank you very much for your suggestion, I will take your advice. I have a problem in SQL, and please expert helps me out and give me an advice of how to solve this issue.
I have a main table where to store the data,
I have mutliple columns and those are
SSN, DECLEAREDDATE, SOMATICMCCLEAREDDATE, PSYCMCCLEAREDDATE, DESECONDCLEAREDDATE, SOMATICMCCLEAREDDATE, PSYCMCSECONDCLEAREDDATE, DDS.
SSN INT, DDS VARCHAR, and the rests are Datetime.
If there is a case (SSN), which has been assigned to one of three columns (only one column) such as DECLEAREDDATE OR SOMATICMCCLEAREDDATE, OR PSYCMCCLEAREDDATE. For example,
SSN is 111223333 is assigned to DECLEAREDDATE then the rests of two columns such as SOMATICMCCLEAREDDATE, and PSYCMCLEAREDDATE should be blanked. If a different case is assigned to SOMATICMCCLEAREDDATE then DECLEAREDDATE and PSYCMCCLEAREDDATE should be blanked. If the column has been assigned to determine the outcome of case, for example like case 111223333 is assigned to DECLEAREDDATE then this column will have a date such as 1/1/2009.
But if there is a case 222334444 has been assigned to SomaticMCClearedDate to determine the outcome, somehow that case returned for the second time. Because the first determination was wrong and second time reviews should be SOMATICMCSECONDCLEAREDDATE, then DESECONDCLEAREDDATE & PSYCMCSECONDCLEAREDDATE should be emptied. But this case won't count as a cleared case with no returns (this case is a return case).
However, if a case likes 111223333 is a cleared case with no returns then it should have the office name where this case was cleared by what office. So DDS is an office name where contains the office variable BO, CT, NH, VT, ME, RI.
I wish to count and display the number of cases cleared with no returns. And the outlook that I wish to have:
Create Table [dbo][WeeklyActualClearedCasewithNoReturns]
(
[SSN][INT]NULL,[DDS][Varchar](3)NULL,[DECLEAREDDATE][Datetime]NULL,[SOMATICMCCLEAREDDATE][Datetime]NULL,[PSYCMCCLEAREDDATE][DATETIME]NULL,[DESECONDCLEAREDDATE][DATETIME]NULL,[SOMATICMCSECONDCLEAREDDATE][DATETIME]NULL, [PSYCMCSECONDCLEAREDDATE][DATETIME]NULL
)ON [PRIMARY]
INSERT INTO WeeklyActualClearedCasewithNoReturns Values('000112222', 'CT', '1/1/2009', ' ', ' ', ' ', ' ', ' ')
INSERT INTO WeeklyActualClearedCasewithNoReturns Values ('111335555', 'ME', ' ', ' ', '1/4/2009', ' ', ' ', ' ')
INSERT INTO WeeklyActualClearedCasewithNoReturns Values ('666223333', 'CT', ' ', '1/6/2009', ' ', ' ', ' ', ' ') INSERT INTO WeeklyActualClearedCasewithNoReturns Values ('999228888', 'RI', ' ', ' ', '1/11/2009', ' ', ' ', ' ')
I have written the codes for this other reports, and they count all cases. But this report I get stuck because of the conditions and that condition are
Count cases cleared with no returns, and SSN is count only once time (SSN is a primary key, so there is no way that SSN can be duplicated or count twice). So I ignore SSN condition in this factor.
If there is a case (SSN), which has been assigned to one of three columns (only one column) such as DECLEAREDDATE OR SOMATICMCCLEAREDDATE, OR PSYCMCCLEAREDDATE. For example,
SSN is 111223333 is assigned to DECLEAREDDATE then the rests of two columns such as SOMATICMCCLEAREDDATE, and PSYCMCLEAREDDATE should be blanked. If a different case is assigned to SOMATICMCCLEAREDDATE then DECLEAREDDATE and PSYCMCCLEAREDDATE should be blanked. If the column has been assigned to determine the outcome of case, for example like case 111223333 is assigned to DECLEAREDDATE then this column will have a date such as 1/1/2009.
But if there is a case 222334444 has been assigned to SomaticMCClearedDate to determine the outcome, somehow that case returned for the second time. Because the first determination was wrong and second time reviews should be SOMATICMCSECONDCLEAREDDATE, then DESECONDCLEAREDDATE & PSYCMCSECONDCLEAREDDATE should be emptied. But this case won't count as a cleared case with no returns (this case is a return case).
However, if a case likes 111223333 is a cleared case with no returns then it should have the office name where this case was cleared by what office. So DDS is an office name where contains the office variable BO, CT, NH, VT, ME, RI.
I wish to count and display the number of cases cleared with no returns. And the outlook that I wish to have:
I have written the codes for this other reports, and they count all cases. But this report I get stuck because of the conditions and that condition are
Count cases cleared with no returns, and SSN is count only once time (SSN is a primary key, so there is no way that SSN can be duplicated or count twice). So I ignore SSN condition in this factor.
ALTER PROCEDURE [dbo].[WklyClearances]
--- Add the parameters for the stored procedure here
@Start Datetime,
@End Datetime,
@Parameter varchar(3) = 'ALL'
AS
BEGIN
--- SET NOCOUNT ON added to prevent extra result sets from
--- interfering with SELECT statements.
SET NOCOUNT ON
Select @Start = COALESCE( @Start, '01-Jan-2000'),
@End = COALESCE( @End, GETDATE() ),
@Parameter = COALESCE( @Parameter, 'ALL')
;WITH AllDDS
AS
(
SELECT DDS, DEClearedDate AS ClearedDate, DECleared AS Cleared
FROM dbo.DECleared
WHERE (DEClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCClearedDate AS ClearedDate, SomaticMCCleared AS Cleared
FROM dbo.SomaticMCCleared
WHERE (SomaticMCClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCClearedDate AS ClearedDate, PsycMCCleared AS Cleared
FROM dbo.PsycMCCleared
WHERE (PsycMCClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, DESecondClearedDate AS ClearedDate, DESecondCleared AS Cleared
FROM dbo.DESecondCleared
WHERE (DESecondClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCSecondClearedDate AS ClearedDate, SomaticMCSecondCleared AS Cleared
FROM dbo.SomaticMCSecondCleared
WHERE (SomaticMCSecondClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCSecondClearedDate AS ClearedDate, PsycMCSecondCleared AS Cleared
FROM dbo.PsycMCSecondCleared
WHERE (PsycMCSecondClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, DEThirdClearedDate AS ClearedDate, DEThirdCleared AS Cleared
FROM dbo.DEThirdCleared
WHERE (DEThirdClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCThirdClearedDate AS ClearedDate, SomaticMCThirdCleared AS Cleared
FROM dbo.SomaticMCThirdCleared
WHERE (SomaticMCThirdClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCThirdClearedDate AS ClearedDate, PsycMCThirdCleared AS Cleared
FROM dbo.PsycMCThirdCleared
WHERE (PsycMCThirdClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, DEFourthClearedDate AS ClearedDate, DEFourthCleared AS Cleared
FROM dbo.DEFourthCleared
WHERE (DEFourthClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, SomaticMCFourthClearedDate AS ClearedDate, SomaticMCFourthCleared AS Cleared
FROM dbo.SomaticMCFourthCleared
WHERE (SomaticMCFourthClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
UNION ALL
SELECT DDS, PsycMCFourthClearedDate AS ClearedDate, PsycMCFourthCleared AS Cleared
FROM dbo.PsycMCFourthCleared
WHERE (PsycMCFourthClearedDate BETWEEN @Start AND @End)
AND ( @Parameter = 'ALL' OR DDS = @Parameter )
),
PivotDDS
AS
(
SELECT ClearedDate,
ISNULL( SUM( ISNULL( [BO], 0 ) ), 0 ) AS [BO],
ISNULL( SUM( ISNULL( [CT], 0 ) ), 0 ) AS [CT],
ISNULL( SUM( ISNULL( [NH], 0 ) ), 0 ) AS [NH],
ISNULL( SUM( ISNULL( [ME], 0 ) ), 0 ) AS [ME],
ISNULL( SUM( ISNULL( [RI], 0 ) ), 0 ) AS [RI],
ISNULL( SUM( ISNULL( [VT], 0 ) ), 0 ) AS [VT],
ISNULL( SUM( ISNULL( [WO], 0 ) ), 0 ) AS [WO]
FROM AllDDS
PIVOT
(
SUM( Cleared ) FOR DDS IN( [BO], [CT], [NH], [ME], [RI], [VT], [WO] )
) P
GROUP BY ClearedDate
),
FinalDDS
AS
(
SELECT ClearedDate, [BO] AS BOCleared, [CT] AS CTCleared, [NH] AS NHCleared,
[ME] AS MECleared, [RI] AS RICleared, [VT] AS VTCleared, [WO] AS WOCleared,
[BO] + [CT] + [NH] + [ME] + [RI] + [VT] + [WO] AS TotalCleared,
( CASE WHEN [BO] > 0 THEN ', BO' ELSE ' ' END )
+ ( CASE WHEN [CT] > 0 THEN ', CT' ELSE ' ' END )
+ ( CASE WHEN [NH] > 0 THEN ', NH' ELSE ' ' END )
+ ( CASE WHEN [ME] > 0 THEN ', ME' ELSE ' ' END )
+ ( CASE WHEN [RI] > 0 THEN ', RI' ELSE ' ' END )
+ ( CASE WHEN [VT] > 0 THEN ', VT' ELSE ' ' END )
+ ( CASE WHEN [WO] > 0 THEN ', WO' ELSE ' ' END ) AS DDS
FROM PivotDDS
)
SELECT *
From (
Select Sum(BOCleared) as BOCleared, Sum(CTCleared) as CTCleared, Sum(NHCleared) as NHCleared, Sum(MECleared) as MECleared, Sum(RICleared) as RICleared, Sum(VTCleared) as VTCleared, Sum(WOCleared) as WOCleared,
Sum(TotalCleared) AS TotalCleared,ClearedDate AS ClearedDate, SUBSTRING( DDS, 3, 1000 ) AS DDS
FROM FinalDDS
GROUP BY ClearedDate, SUBSTRING(DDS, 3, 1000) WITH ROLLUP
)D
Where (ClearedDate IS NULL AND DDS IS NULL) OR (ClearedDate IS NOT NULL AND DDS IS NOT NULL)
Order BY ISNULL( ClearedDate, '31-Dec-2090')
Thank you
April 3, 2009 at 3:18 pm
Hi Joseph,
I am not sure if i had any words which felt like insulting. If that is the case then i am sorry but this was not my intention. I suggested an alternative. In did implemented similar thing for one of my client.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply