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)
 Percentile Calculation for each value

Author  Topic 

Yuveka
Starting Member

2 Posts

Posted - 2014-12-29 : 09:22:48
Hi All,
I am using sql server 2008 to calculate the percentile value for each column.Any help on this is appreciated.

CREATE TABLE #Table (Score Varchar(4),Percentile int)
INSERT INTO #Table (Score)
VALUES
('80'),
('55'),
('125'),
('99'),
('75'),
('130'),
('37'),
('73'),
('151')

select * from #Table


Thanks,
Venu

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2014-12-29 : 09:35:27
what output do u want?

Vijay is here to learn something from you guys.
Go to Top of Page

Yuveka
Starting Member

2 Posts

Posted - 2014-12-29 : 10:16:40
I would like the result to be shown in 25,50,75,100 percentile values
Here is the example

Score | percentile
80 | 75
37 | 25
151 | 100
75 | 50

Thanks,
Venu
Go to Top of Page

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2014-12-29 : 19:04:24
What is the logic to calculate Percentile ? Ex : how do you get the 75 for the first row ?
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-12-29 : 19:15:22
I changed the Score column to be of type Int instead of varchar.
select score, ntile(4) over(order by score) * 25 Percentile 
from #Table
order by score;
???



No amount of belief makes something a fact. -James Randi
Go to Top of Page
   

- Advertisement -