Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Summary data

Author  Topic 

avkuvalekar
Starting Member

38 Posts
Posted - 2008-06-18 : 03:21:42
Hi,

Once again some doubt!

I have a query as below -
Select TeamName ,
SUM(CASE WHEN Team_Date = '01/07/2007' THEN Team_Total ELSE 0 END) AS [Jul 07] ,
SUM(CASE WHEN Team_Date = '01/08/2007' THEN Team_Total ELSE 0 END) AS [Aug 07] ,
SUM(CASE WHEN Team_Date = '01/09/2007' THEN Team_Total ELSE 0 END) AS [Sep 07] ,
SUM(CASE WHEN Team_Date = '01/10/2007' THEN Team_Total ELSE 0 END) AS [Oct 07] ,
SUM(CASE WHEN Team_Date = '01/11/2007' THEN Team_Total ELSE 0 END) AS [Nov 07] ,
SUM(CASE WHEN Team_Date = '01/12/2007' THEN Team_Total ELSE 0 END) AS [Dec 07] ,
SUM(CASE WHEN Team_Date = '01/01/2008' THEN Team_Total ELSE 0 END) AS [Jan 08] ,
SUM(CASE WHEN Team_Date = '01/02/2008' THEN Team_Total ELSE 0 END) AS [Feb 08] ,
SUM(CASE WHEN Team_Date = '01/03/2008' THEN Team_Total ELSE 0 END) AS [Mar 08] ,
SUM(CASE WHEN Team_Date = '01/04/2008' THEN Team_Total ELSE 0 END) AS [Apr 08] ,
SUM(CASE WHEN Team_Date = '01/05/2008' THEN Team_Total ELSE 0 END) AS [May 08] ,
SUM(CASE WHEN Team_Date = '01/06/2008' THEN Team_Total ELSE 0 END) AS [Jun 08]
FROM dbo.uView_DimHC_Team_Details_View1 where TeamParentID < '3' GROUP BY TeamName ORDER BY TeamName

This basically creates a table where I have team names as rows, month names as columns and team strength as each value.
Now I want to add one row at the bottom which gives a summary which basically calculates all the values in that column. I am displaying this on web where I can do this using gridview but the problem is I am using the same gridview for 2-3 different queries which have different columns so the rowdatabound method can not be used.
How can I get aggregate row at the end of table from this table?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-18 : 04:08:06
Either do a UNION
or
do it your gridview. add an empty row and do the calculations there.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

avkuvalekar
Starting Member

38 Posts
Posted - 2008-06-18 : 04:19:50
Can you please elaborate??
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-18 : 04:20:59
Or add the
WITH ROLLUP

operator after GROUP BY but before ORDER BY.

This may give you an idea

SELECT		Number,
		SUM(high)
FROM		master..spt_values
WHERE		Type = 'p'
		AND Number BETWEEN 0 AND 7
GROUP BY	Number
WITH ROLLUP
ORDER BY	CASE
			WHEN Number IS NULL THEN 1
			ELSE 0
		END,
		Number

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

avkuvalekar
Starting Member

38 Posts
Posted - 2008-06-18 : 04:29:52
Hi,

I have used the following statement -


Select TeamName ,
sum(CASE WHEN Team_Date = '01/07/2007' THEN Team_Total ELSE 0 END) AS [Jul 07] ,
SUM(CASE WHEN Team_Date = '01/08/2007' THEN Team_Total ELSE 0 END) AS [Aug 07] ,
SUM(CASE WHEN Team_Date = '01/09/2007' THEN Team_Total ELSE 0 END) AS [Sep 07] ,
SUM(CASE WHEN Team_Date = '01/10/2007' THEN Team_Total ELSE 0 END) AS [Oct 07] ,
SUM(CASE WHEN Team_Date = '01/11/2007' THEN Team_Total ELSE 0 END) AS [Nov 07] ,
SUM(CASE WHEN Team_Date = '01/12/2007' THEN Team_Total ELSE 0 END) AS [Dec 07] ,
SUM(CASE WHEN Team_Date = '01/01/2008' THEN Team_Total ELSE 0 END) AS [Jan 08] ,
SUM(CASE WHEN Team_Date = '01/02/2008' THEN Team_Total ELSE 0 END) AS [Feb 08] ,
SUM(CASE WHEN Team_Date = '01/03/2008' THEN Team_Total ELSE 0 END) AS [Mar 08] ,
SUM(CASE WHEN Team_Date = '01/04/2008' THEN Team_Total ELSE 0 END) AS [Apr 08] ,
SUM(CASE WHEN Team_Date = '01/05/2008' THEN Team_Total ELSE 0 END) AS [May 08] ,
SUM(CASE WHEN Team_Date = '01/06/2008' THEN Team_Total ELSE 0 END) AS [Jun 08]
FROM dbo.uView_DimHC_Team_Details_View1 where TeamParentID < '3'

GROUP BY TeamName ORDER BY TeamName

UNION
Select 'Summary' ,
sum(CASE WHEN Team_Date = '01/07/2007' THEN Team_Total ELSE 0 END) AS [Jul 07] ,
SUM(CASE WHEN Team_Date = '01/08/2007' THEN Team_Total ELSE 0 END) AS [Aug 07] ,
SUM(CASE WHEN Team_Date = '01/09/2007' THEN Team_Total ELSE 0 END) AS [Sep 07] ,
SUM(CASE WHEN Team_Date = '01/10/2007' THEN Team_Total ELSE 0 END) AS [Oct 07] ,
SUM(CASE WHEN Team_Date = '01/11/2007' THEN Team_Total ELSE 0 END) AS [Nov 07] ,
SUM(CASE WHEN Team_Date = '01/12/2007' THEN Team_Total ELSE 0 END) AS [Dec 07] ,
SUM(CASE WHEN Team_Date = '01/01/2008' THEN Team_Total ELSE 0 END) AS [Jan 08] ,
SUM(CASE WHEN Team_Date = '01/02/2008' THEN Team_Total ELSE 0 END) AS [Feb 08] ,
SUM(CASE WHEN Team_Date = '01/03/2008' THEN Team_Total ELSE 0 END) AS [Mar 08] ,
SUM(CASE WHEN Team_Date = '01/04/2008' THEN Team_Total ELSE 0 END) AS [Apr 08] ,
SUM(CASE WHEN Team_Date = '01/05/2008' THEN Team_Total ELSE 0 END) AS [May 08] ,
SUM(CASE WHEN Team_Date = '01/06/2008' THEN Team_Total ELSE 0 END) AS [Jun 08]
FROM dbo.uView_DimHC_Team_Details_View1 where TeamParentID < '3'

The error is "Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'UNION'."

Individually both the queries are working fine and have equal number of columns. any help?
Go to Top of Page

avkuvalekar
Starting Member

38 Posts
Posted - 2008-06-18 : 04:37:31
The with rollup command gave me the total but in first row and with TeamName as NULL, how can I change that to Summary and display it as a last row and not first?

Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-18 : 05:11:09
quote:
Originally posted by avkuvalekar

Hi,

I have used the following statement -

SELECT * FROM
(
Select TeamName ,
sum(CASE WHEN Team_Date = '01/07/2007' THEN Team_Total ELSE 0 END) AS [Jul 07] ,
SUM(CASE WHEN Team_Date = '01/08/2007' THEN Team_Total ELSE 0 END) AS [Aug 07] ,
SUM(CASE WHEN Team_Date = '01/09/2007' THEN Team_Total ELSE 0 END) AS [Sep 07] ,
SUM(CASE WHEN Team_Date = '01/10/2007' THEN Team_Total ELSE 0 END) AS [Oct 07] ,
SUM(CASE WHEN Team_Date = '01/11/2007' THEN Team_Total ELSE 0 END) AS [Nov 07] ,
SUM(CASE WHEN Team_Date = '01/12/2007' THEN Team_Total ELSE 0 END) AS [Dec 07] ,
SUM(CASE WHEN Team_Date = '01/01/2008' THEN Team_Total ELSE 0 END) AS [Jan 08] ,
SUM(CASE WHEN Team_Date = '01/02/2008' THEN Team_Total ELSE 0 END) AS [Feb 08] ,
SUM(CASE WHEN Team_Date = '01/03/2008' THEN Team_Total ELSE 0 END) AS [Mar 08] ,
SUM(CASE WHEN Team_Date = '01/04/2008' THEN Team_Total ELSE 0 END) AS [Apr 08] ,
SUM(CASE WHEN Team_Date = '01/05/2008' THEN Team_Total ELSE 0 END) AS [May 08] ,
SUM(CASE WHEN Team_Date = '01/06/2008' THEN Team_Total ELSE 0 END) AS [Jun 08]
FROM dbo.uView_DimHC_Team_Details_View1 where TeamParentID < '3'

GROUP BY TeamName
ORDER BY TeamName)t

UNION ALL

Select 'Summary' ,
sum(CASE WHEN Team_Date = '01/07/2007' THEN Team_Total ELSE 0 END) AS [Jul 07] ,
SUM(CASE WHEN Team_Date = '01/08/2007' THEN Team_Total ELSE 0 END) AS [Aug 07] ,
SUM(CASE WHEN Team_Date = '01/09/2007' THEN Team_Total ELSE 0 END) AS [Sep 07] ,
SUM(CASE WHEN Team_Date = '01/10/2007' THEN Team_Total ELSE 0 END) AS [Oct 07] ,
SUM(CASE WHEN Team_Date = '01/11/2007' THEN Team_Total ELSE 0 END) AS [Nov 07] ,
SUM(CASE WHEN Team_Date = '01/12/2007' THEN Team_Total ELSE 0 END) AS [Dec 07] ,
SUM(CASE WHEN Team_Date = '01/01/2008' THEN Team_Total ELSE 0 END) AS [Jan 08] ,
SUM(CASE WHEN Team_Date = '01/02/2008' THEN Team_Total ELSE 0 END) AS [Feb 08] ,
SUM(CASE WHEN Team_Date = '01/03/2008' THEN Team_Total ELSE 0 END) AS [Mar 08] ,
SUM(CASE WHEN Team_Date = '01/04/2008' THEN Team_Total ELSE 0 END) AS [Apr 08] ,
SUM(CASE WHEN Team_Date = '01/05/2008' THEN Team_Total ELSE 0 END) AS [May 08] ,
SUM(CASE WHEN Team_Date = '01/06/2008' THEN Team_Total ELSE 0 END) AS [Jun 08]
FROM dbo.uView_DimHC_Team_Details_View1 where TeamParentID < '3'

The error is "Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'UNION'."

Individually both the queries are working fine and have equal number of columns. any help?


Change like this and try
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-18 : 05:36:17
quote:
Originally posted by avkuvalekar

The with rollup command gave me the total but in first row and with TeamName as NULL, how can I change that to Summary and display it as a last row and not first?

Thanks!


See my post 06/18/2008 : 04:20:59


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

avkuvalekar
Starting Member

38 Posts
Posted - 2008-06-18 : 23:29:58
Hi,

That union all worked...now I have to figure out how to make it bold in gridview. My next question is -


select COUNt(case when EmpType = 'Permanent' THEN EmpType ELSE NULL END)AS PermCount ,
COUNt(case when EmpType = 'Contractor' THEN EmpType ELSE NULL END) AS ContCount,
COUNt(case when EmpLevel = 'Exec' THEN EmpType ELSE NULL END) AS ExecCount,
COUNt(case when EmpLevel = 'Manager' THEN EmpType ELSE NULL END) AS MgrCount,
COUNt(case when EmpLevel = 'AD' THEN EmpType ELSE NULL END) AS ADCount,
sum(dbo.DimHC_Team_Config.Team_Head_Count) - COUNt(case when EmpType = 'Contractor' THEN EmpType ELSE NULL END)
- COUNt(case when EmpType = 'Permanent' THEN EmpType ELSE NULL END) as Open_Posn,
COUNt(case when EmpType = 'Contractor' THEN EmpType ELSE NULL END) +
COUNt(case when EmpType = 'Permanent' THEN EmpType ELSE NULL END) as Total from dbo.DimHC_Team_Config, dbo.uview_DimHC_Emp_view1 ev

In this query I am getting the output as

1680 0 1552 64 64 20980 1680

which is essentially 16 times the actual count! probably because some kind of multiplication is happening for TeamID because
of sum(dbo.DimHC_Team_Config.Team_Head_Count) this statement as without this I am getting a perfectly correct answer.

As you can see, all I am trying to do here is calculate the sum of all employees, take the sum of team head counts and then display it as Total, No of Employees, No of open positions, Head Count (Max posn)

I will then UNION ALL this result with the output of

SELECT tc.TeamID, TeamName,
COUNt(case when EmpType = 'Permanent' THEN EmpType ELSE NULL END)AS PermCount,
COUNt(case when EmpType = 'Contractor' THEN EmpType ELSE NULL END) AS ContCount,
COUNt(case when EmpLevel = 'Exec' THEN EmpType ELSE NULL END) AS ExecCount,
COUNt(case when EmpLevel = 'Manager' THEN EmpType ELSE NULL END) AS MgrCount,
COUNt(case when EmpLevel = 'AD' THEN EmpType ELSE NULL END) AS ADCount,
tc.Team_Head_Count - COUNt(case when EmpType = 'Contractor' THEN EmpType ELSE NULL END)
- COUNt(case when EmpType = 'Permanent' THEN EmpType ELSE NULL END) as Open_Posn,
COUNt(case when EmpType = 'Contractor' THEN EmpType ELSE NULL END) +
COUNt(case when EmpType = 'Permanent' THEN EmpType ELSE NULL END) as Total,
tc.Team_Head_Count
FROM dbo.uview_DimHC_Emp_view1 ev
LEFT JOIN dbo.DimHC_Team_Config tc
ON ev.TeamID = tc.TeamID where StartDate < = '19/06/2008' and
(EndDate = '01/01/1753' or EndDate > '19/06/2008' ) and TeamParentID < '3'
GROUP BY tc.TeamID, TeamName , tc.Team_Head_Count

which gives me team wise counts.
What am I missing here?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-19 : 02:19:26
quote:
Originally posted by avkuvalekar

Hi,

That union all worked...now I have to figure out how to make it bold in gridview. My next question is -


select COUNt(case when EmpType = 'Permanent' THEN EmpType ELSE NULL END)AS PermCount ,
COUNt(case when EmpType = 'Contractor' THEN EmpType ELSE NULL END) AS ContCount,
COUNt(case when EmpLevel = 'Exec' THEN EmpType ELSE NULL END) AS ExecCount,
COUNt(case when EmpLevel = 'Manager' THEN EmpType ELSE NULL END) AS MgrCount,
COUNt(case when EmpLevel = 'AD' THEN EmpType ELSE NULL END) AS ADCount,
sum(dbo.DimHC_Team_Config.Team_Head_Count) - COUNt(case when EmpType = 'Contractor' THEN EmpType ELSE NULL END)
- COUNt(case when EmpType = 'Permanent' THEN EmpType ELSE NULL END) as Open_Posn,
COUNt(case when EmpType = 'Contractor' THEN EmpType ELSE NULL END) +
COUNt(case when EmpType = 'Permanent' THEN EmpType ELSE NULL END) as Total from dbo.DimHC_Team_Config, dbo.uview_DimHC_Emp_view1 ev

In this query I am getting the output as

1680 0 1552 64 64 20980 1680

which is essentially 16 times the actual count! probably because some kind of multiplication is happening for TeamID because
of sum(dbo.DimHC_Team_Config.Team_Head_Count) this statement as without this I am getting a perfectly correct answer.

As you can see, all I am trying to do here is calculate the sum of all employees, take the sum of team head counts and then display it as Total, No of Employees, No of open positions, Head Count (Max posn)

I will then UNION ALL this result with the output of

SELECT tc.TeamID, TeamName,
COUNt(case when EmpType = 'Permanent' THEN EmpType ELSE NULL END)AS PermCount,
COUNt(case when EmpType = 'Contractor' THEN EmpType ELSE NULL END) AS ContCount,
COUNt(case when EmpLevel = 'Exec' THEN EmpType ELSE NULL END) AS ExecCount,
COUNt(case when EmpLevel = 'Manager' THEN EmpType ELSE NULL END) AS MgrCount,
COUNt(case when EmpLevel = 'AD' THEN EmpType ELSE NULL END) AS ADCount,
tc.Team_Head_Count - COUNt(case when EmpType = 'Contractor' THEN EmpType ELSE NULL END)
- COUNt(case when EmpType = 'Permanent' THEN EmpType ELSE NULL END) as Open_Posn,
COUNt(case when EmpType = 'Contractor' THEN EmpType ELSE NULL END) +
COUNt(case when EmpType = 'Permanent' THEN EmpType ELSE NULL END) as Total,
tc.Team_Head_Count
FROM dbo.uview_DimHC_Emp_view1 ev
LEFT JOIN dbo.DimHC_Team_Config tc
ON ev.TeamID = tc.TeamID where StartDate < = '19/06/2008' and
(EndDate = '01/01/1753' or EndDate > '19/06/2008' ) and TeamParentID < '3'
GROUP BY tc.TeamID, TeamName , tc.Team_Head_Count

which gives me team wise counts.
What am I missing here?



Will there be multiple records for team in DimHC_Team_Config?
Go to Top of Page

avkuvalekar
Starting Member

38 Posts
Posted - 2008-06-20 : 01:14:23
No, in the config table there will be just one entry of one team and there are 16 teams at the moment.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-20 : 01:59:37
quote:
Originally posted by avkuvalekar

No, in the config table there will be just one entry of one team and there are 16 teams at the moment.

And are you grouping by team id in first query? if not then that ight be reason for arrival of large figures than expected.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-20 : 02:11:17
[code]SELECT COALESCE(v.TeamName, 'Total') AS TeamName,
SUM(CASE WHEN v.Team_Date = '01/07/2007' THEN v.Team_Total ELSE 0 END) AS [Jul 07],
SUM(CASE WHEN v.Team_Date = '01/08/2007' THEN v.Team_Total ELSE 0 END) AS [Aug 07],
SUM(CASE WHEN v.Team_Date = '01/09/2007' THEN v.Team_Total ELSE 0 END) AS [Sep 07],
SUM(CASE WHEN v.Team_Date = '01/10/2007' THEN v.Team_Total ELSE 0 END) AS [Oct 07],
SUM(CASE WHEN v.Team_Date = '01/11/2007' THEN v.Team_Total ELSE 0 END) AS [Nov 07],
SUM(CASE WHEN v.Team_Date = '01/12/2007' THEN v.Team_Total ELSE 0 END) AS [Dec 07],
SUM(CASE WHEN v.Team_Date = '01/01/2008' THEN v.Team_Total ELSE 0 END) AS [Jan 08],
SUM(CASE WHEN v.Team_Date = '01/02/2008' THEN v.Team_Total ELSE 0 END) AS [Feb 08],
SUM(CASE WHEN v.Team_Date = '01/03/2008' THEN v.Team_Total ELSE 0 END) AS [Mar 08],
SUM(CASE WHEN v.Team_Date = '01/04/2008' THEN v.Team_Total ELSE 0 END) AS [Apr 08],
SUM(CASE WHEN v.Team_Date = '01/05/2008' THEN v.Team_Total ELSE 0 END) AS [May 08],
SUM(CASE WHEN v.Team_Date = '01/06/2008' THEN v.Team_Total ELSE 0 END) AS [Jun 08]
FROM dbo.uView_DimHC_Team_Details_View1 AS v
WHERE v.TeamParentID < '3'
GROUP BY v.TeamName
WITH ROLLUP
ORDER BY CASE
WHEN v.TeamName IS NULL THEN 1
ELSE 0
END,
v.TeamName[/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site