Count - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

Count

Author

Topic

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-08-26 : 00:10:24

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
Aged Yak Warrior

545 Posts

Posted - 2014-08-26 : 01:05:03

[code]
;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)

[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-08-26 : 01:05:20

output:


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

sabinWeb MCP

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-08-26 : 15:18:04

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

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-08-27 : 01:43:48

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

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-08-27 : 01:53:44

There are a lot of examples:
check this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources