How can I group student scores into quintile using

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 2008 Forums

Transact-SQL (2008)

How can I group student scores into quintile using

Author

Topic

MikeSQLIT
Starting Member

4 Posts

Posted - 2014-07-24 : 00:18:22

Can anyone help me to group student scores into quintile. I think there is a feature in SQL Server 2012, but still we are have not upgrade to it as we are using 2008R2. I tried Ntile(5) but it not generating the desired result. I need below Quintile Column

Student Score Quintile.

Student1 20 1

Student2 20 1

Student3 30 2

Student4 30 2

Student5 40 2

Student6 40 2

Student7 50 3

Student8 50 3

Student9 60 3

Student10 70 4

Student11 70 4

Student12 80 4

Student13 80 4

Student14 90 5

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-24 : 01:03:17

[code]
;With Student
AS
(SELECT Student ,Score ,Quintile
FROM (VALUES('Student1',20,1) ,('Student2', 20, 1),('Student3' ,30 ,2),('Student4', 30 ,2),('Student5', 40 ,2),('Student6', 40, 2),('Student7', 50, 3),
('Student8', 50, 3),('Student9', 60, 3),('Student10', 70, 4),('Student11', 70 ,4),('Student12', 80, 4)
,('Student13' ,80, 4),('Student14', 90, 5)) X(Student ,Score ,Quintile)
)

,QInterval
AS
( SELECT 0 as lowLimit,20 as UpperLimit ,1 as Interval UNION ALL
SELECT 20 ,40 ,2 UNION ALL
SELECT 40,60 ,3 UNION ALL
SELECT 60,80 ,4 UNION ALL
SELECT 80,100,5)

SELECT S.Student , S.Score , I.Interval AS Quintile
FROM Student AS S
INNER JOIN QInterval AS I
ON S.Score > I.LowLimit
AND S.Score<=I.UpperLimit
[/code]

output:
[code]
Student Score Quintile
Student1 20 1
Student2 20 1
Student3 30 2
Student4 30 2
Student5 40 2
Student6 40 2
Student7 50 3
Student8 50 3
Student9 60 3
Student10 70 4
Student11 70 4
Student12 80 4
Student13 80 4
Student14 90 5
[/code]

sabinWeb MCP

MikeSQLIT
Starting Member

4 Posts

Posted - 2014-07-24 : 04:43:10

Hi Sabin,
Thanks for your reply. However, my data generated consists of dynamic records, hundreds of records. The score values may also vary. So i need a dynamic SQL query which can work on a set of 100 records or 3000 records and also it should be capable of sorting any value either in 1s or 10s. The above quoted by me was only a sample. Can you help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-24 : 04:47:46

(Score - 1) / 20 * 20 + 1

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

MikeSQLIT
Starting Member

4 Posts

Posted - 2014-07-24 : 10:15:16

Hi...I got the required result. Blam helped me.
DECLARE @Students TABLE (StudentID INT IDENTITY(1,1), StudentName VARCHAR(20), Score INT)

INSERT INTO @Students(StudentName, Score)
VALUES ('Student 1', 20), ('Student 2', 20),
('Student 3', 30), ('Student 4', 30),
('Student 5', 40), ('Student 6', 40),
('Student 7', 50), ('Student 8', 50),
('Student 9', 60), ('Student 10', 70),
('Student 11', 70),('Student 12', 80),
('Student 13', 80),('Student 14', 90)

SELECT s.StudentName, s.Score, qm.maxQ
FROM @Students as s
join ( select score, MAX(Quintile) as maxQ
from ( SELECT Score, Quintile = NTILE(5) OVER(ORDER BY Score)
FROM @Students ) q
group by q.score ) qm
on qm.Score = s.Score

Thanks a ton Blam!!!!

MikeSQLIT
Starting Member

4 Posts

Posted - 2014-07-25 : 00:42:21

Hi , Above worked perfectly for small set of data. However, i tried larger data set with 100 records. It failed. It is not at all showing Rank 3. Below is the 99 records. Student Name Score Student 1 to 5 -> 4 Student 7 to 26 -> 5 Student 27 to 71 -> 6 Student 72 to 98 -> 7 Student 99 -> 9 . Can anybody look into it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources