SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLBoy14
Starting Member

USA
38 Posts

Posted - 08/26/2014 :  00:10:24  Show Profile  Reply with Quote
Hi,

Can anyone help me with the Count sql query? Below are the dataset:

SELECT Level, Teacher, StudentID
FROM Schooldatabase

Level Teacher StudentID
A Andy 112233
B Tony 112233
C Sam 123344
D Dean 554455
E Daniel 554455

How do I write the sql qeury to have the output as below:

Level Teacher StudentID Count
A,B Andy, Tony 112233 2
C Sam 123344 1
D, E Dean, Daniel 554455 2

Thank you all


SQLBoy

stepson
Constraint Violating Yak Guru

Romania
425 Posts

Posted - 08/26/2014 :  01:05:03  Show Profile  Reply with Quote

;with aTable(Level,Teacher,StudentID)
AS
	(SELECT 'A', 'Andy', 112233 UNION ALL
	SELECT 'B', 'Tony', 112233 UNION ALL
	SELECT 'C', 'Sam', 123344 UNION ALL
	SELECT 'D', 'Dean', 554455  UNION ALL
	SELECT 'E', 'Daniel', 554455)


SELECT	
	STUFF(x.lvl,1,1,'') as Level
	,STUFF(y.Teachers,1,1,'') as Teachers
	,Tbl.StudentID
	,Tbl.[Count] as [Count]
FROM
	(
		SELECT 
			StudentID
			,COUNT(StudentID) as [COUNT]
		FROM aTable 
		GROUP BY 
			StudentID
	) AS Tbl
	CROSS APPLY
	(
			SELECT
				', ' + A.Level 				
			FROM
				aTable AS A	
			WHERE A.StudentID = Tbl.StudentID
			FOR XML PATH(''))X(lvl)
	CROSS APPLY
	(
			SELECT
				', ' + A.Teacher
			FROM
				aTable AS A	
			WHERE A.StudentID = Tbl.StudentID
			FOR XML PATH(''))Y(Teachers)





sabinWeb MCP
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
425 Posts

Posted - 08/26/2014 :  01:05:20  Show Profile  Reply with Quote
output:

Level	Teachers	StudentID	Count
 A, B	 Andy, Tony	112233	2
 C	 Sam	123344	1
 D, E	 Dean, Daniel	554455	2



sabinWeb MCP
Go to Top of Page

SQLBoy14
Starting Member

USA
38 Posts

Posted - 08/26/2014 :  15:18:04  Show Profile  Reply with Quote
Hello Stepson. On your select statement:
(SELECT 'A', 'Andy', 112233 UNION ALL
SELECT 'B', 'Tony', 112233 UNION ALL
SELECT 'C', 'Sam', 123344 UNION ALL
SELECT 'D', 'Dean', 554455 UNION ALL
SELECT 'E', 'Daniel', 554455)


if I have 1000 records, how can I handle each detail?

Thank you

SQLBoy
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
425 Posts

Posted - 08/27/2014 :  01:43:48  Show Profile  Reply with Quote
1000 records is not much.

You can tested and see how it does.
Keep in mind, we don't know your table/structure. Do you have indecs?


here is a sample of testing:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 25 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.



IF Object_ID('tempDB..#aTable') IS NOT NULL
BEGIN
		Drop Table tempDB..#aTable
END
/*
CREATE TABLE #aTable
(iIndex INT IDENTITY(1,1) 
	,[Level] CHAR(1)
	,[Teacher] VARCHAR(30)
	,[StudentID] INT
)

INSERT INTO #aTable(Level,Teacher,StudentID)
VALUES(char(rand()*26+65)
	,	char(rand()*26+65) + char(rand()*26+65) + char(rand()*26+65) + char(rand()*26+65) +char(rand()*26+65)	
	,	112233--char(rand()*9+48) + char(rand()*9+48) +char(rand()*9+48) +char(rand()*9+48)+char(rand()*9+48)+char(rand()*9+48)
	)
Go 250

INSERT INTO #aTable(Level,Teacher,StudentID)
VALUES(char(rand()*26+65)
	,	char(rand()*26+65) + char(rand()*26+65) + char(rand()*26+65) + char(rand()*26+65) +char(rand()*26+65)	
	,	123344--char(rand()*9+48) + char(rand()*9+48) +char(rand()*9+48) +char(rand()*9+48)+char(rand()*9+48)+char(rand()*9+48)
	)
Go 250

INSERT INTO #aTable(Level,Teacher,StudentID)
VALUES(char(rand()*26+65)
	,	char(rand()*26+65) + char(rand()*26+65) + char(rand()*26+65) + char(rand()*26+65) +char(rand()*26+65)	
	,	554455--char(rand()*9+48) + char(rand()*9+48) +char(rand()*9+48) +char(rand()*9+48)+char(rand()*9+48)+char(rand()*9+48)
	)
Go 250

INSERT INTO #aTable(Level,Teacher,StudentID)
VALUES(char(rand()*26+65)
	,	char(rand()*26+65) + char(rand()*26+65) + char(rand()*26+65) + char(rand()*26+65) +char(rand()*26+65)	
	,	778899--char(rand()*9+48) + char(rand()*9+48) +char(rand()*9+48) +char(rand()*9+48)+char(rand()*9+48)+char(rand()*9+48)
	)
Go 250
*/


--SELECT * from #aTable

set statistics io on
set statistics time on 

SELECT	
	STUFF(x.lvl,1,1,'') as Level
	,STUFF(y.Teachers,1,1,'') as Teachers
	,Tbl.StudentID
	,Tbl.[Count] as [Count]
FROM
	(
		SELECT 
			StudentID
			,COUNT(StudentID) as [COUNT]
		FROM #aTable 
		GROUP BY 
			StudentID
	) AS Tbl
	CROSS APPLY
	(
			SELECT
				', ' + A.Level 				
			FROM
				#aTable AS A	
			WHERE A.StudentID = Tbl.StudentID
			FOR XML PATH(''))X(lvl)
	CROSS APPLY
	(
			SELECT
				', ' + A.Teacher
			FROM
				#aTable AS A	
			WHERE A.StudentID = Tbl.StudentID
			FOR XML PATH(''))Y(Teachers)


set statistics io off
set statistics time off 



sabinWeb MCP
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
425 Posts

Posted - 08/27/2014 :  01:53:44  Show Profile  Reply with Quote
There are a lot of examples:
check this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254


sabinWeb MCP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000