Query Help -- Percentile Calculation for each value

Question

Post reply

Query Help -- Percentile Calculation for each value

gosven07

Old Hand

Points: 305
More actions
December 29, 2014 at 10:35 am

#293264

Hi All,
I am using sql server 2008 to calculate the percentile value for each column.Any help on this is appreciated.
--Script
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
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

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110651 More actions · Answer 1

How about the following:

DECLARE @Table AS TABLE (Score Varchar(4), Percentile int);

INSERT INTO @Table (Score)

VALUES ('80'),('55'),('125'),('99'),('75'),('130'),('37'),('73'),('151');

WITH RANKS AS (

SELECT *, NTILE(4) OVER (ORDER BY CAST(Score AS int)) AS QUARTILE

FROM @Table

),

ASSIGNS AS (

SELECT 1 AS RN, 25 AS Percentile UNION ALL

SELECT 2, 50 UNION ALL

SELECT 3, 75 UNION ALL

SELECT 4, 100

),

GROUPED AS (

SELECT MIN(CAST(R.Score AS int)) AS Score, R.QUARTILE

FROM RANKS AS R

GROUP BY R.QUARTILE

)

SELECT G.Score, A.Percentile

FROM GROUPED AS G

INNER JOIN ASSIGNS AS A

ON G.QUARTILE = A.RN

ORDER BY G.QUARTILE;

It uses the NTILE function to distribute the rows into 1 of 4 "tiles". Then it takes the minimum score associated with each tile. Is that what you are looking for?

EDIT: I just realized the output doesn't match your desired results, so I fixed a problem with the query, but still don't get the exact same results, so you'll need to determine exactly what your criteria need to be...

Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)

gosven07 Old Hand Points: 305 More actions · Answer 2

Thanks for the help,

Is there a way that we can assign this percentile values(25,50,75,100) to each field in the table in the same query?

sgmunson SSC Guru Points: 110651 More actions · Answer 3

Try the following:

DECLARE @Table AS TABLE (Score Varchar(4), Percentile int);

INSERT INTO @Table (Score)

VALUES ('80'),('55'),('125'),('99'),('75'),('130'),('37'),('73'),('151');

WITH RANKS AS (

SELECT *, NTILE(4) OVER (ORDER BY CAST(Score AS int)) AS QUARTILE

FROM @Table

),

ASSIGNS AS (

SELECT 1 AS RN, 25 AS Percentile UNION ALL

SELECT 2, 50 UNION ALL

SELECT 3, 75 UNION ALL

SELECT 4, 100

)

SELECT CAST(R.Score AS int) AS Score, A.Percentile

FROM RANKS AS R

INNER JOIN ASSIGNS AS A

ON R.QUARTILE = A.RN

ORDER BY R.QUARTILE;

Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)

gosven07 Old Hand Points: 305 More actions · Answer 4

With this query the result seems not desirable,it is dividing the total records by 4 quartiles blindly.

But my required answer should be in the Ascending order of the records in 4 quartiles.

Example Record set (1,9,3,7,4,8,10,5,2,6,11,12)

The Answer i am getting with this query is (1,9,3)---25 percentile

(7,4,8)---50 percentile etc...

Desired Answer :

(1,2,3)---25 percentile

(4,5,6) ---50 percentile

(7,8,9)---75 percentile

(10,11,12) ---100 percentile

Thanks,

Venu

sgmunson SSC Guru Points: 110651 More actions · Answer 5

Did you have a typo in your desired results? You have the same percentile in the last two sets of numbers as you have for the first two. Also, if you want to process the records in order, then you need a field that can provide that order. If I can't sort by the value of the Score, then I need an alternative, or none of this query can work. Is there either a date/time inserted or other similar field that reflects the desired order? That field would need to be substituted in places where I have CAST the Score field AS int. Let me know...

Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

So change your ORDER BY clause to suit what order you're after.

ORDER BY r.QUARTILE, CAST(R.Score AS int);

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

sgmunson SSC Guru Points: 110651 More actions · Answer 7

Also, instead of NTILE(4), the ROW_NUMBER() function would need to be used, with the OVER clause including the new sorting field I mentioned previously.

Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)

gosven07 Old Hand Points: 305 More actions · Answer 8

gosven07

Old Hand

Points: 305

December 29, 2014 at 2:08 pm

#1766861

yes it was a typo..

sgmunson SSC Guru Points: 110651 More actions · Answer 9

The results from the final query I supplied are as follows:

Score Percentile

===== ========

37 25

55 25

73 25

75 50

80 50

99 75

125 75

130 100

151 100

Is this in some way incorrect ? Please be sure you have the query correct, as I recall making an update on at least one of them. Please let me know if the above results are not what you are looking for, given the supplied data.

Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)

Query Help -- Percentile Calculation for each value

Cookies on SQLServerCentral