Calculate Percentile on Sql 2008

Question

Calculate Percentile on Sql 2008

glarry

Mr or Mrs. 500

Points: 572
More actions
February 29, 2016 at 9:39 am

#311735

Hello,
I start again, my last Topic was deleted.
I'm trying to make something like this:
From table:
IDVal_1 Val_2
2351015
2351511
2371717
2352323
2383425
2382524
2382521
237231
2372512
to:
IDVal1_Perc_50Val1_Perc_97.5Val2_Perc_50Val2_Perc_97.5
235
237
238
Is there a possibility with SQL to do it?
Thank a lot
Regards

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

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

sgmunson SSC Guru Points: 110651 More actions · Answer 1

To be honest, I don't think anyone is going to have any idea what you are trying to accomplish. Calculating a percentile value is something where you need to understand what the data represents in order to do it correctly, and you've supplied absolutely no details. You'll have to describe what value you are looking for, as the quantity of data you presented doesn't generally qualify to make a percentile value worthwhile. Please be detailed in describing what you're looking for.

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

glarry Mr or Mrs. 500 Points: 572 More actions · Answer 2

Hi

In my example I have one table with a column ID and two colums with value.

What I would like to do is this:

Col_IDCol1_50_PercentileCol1_97_PercentileCol2_50_PercentileCol2_97_Percentile

235

237

238

For the column 'Col1_50_Percentile' the 50 Percentile of column 'Val_1' for the ID '235',

For the column 'Col1_97_Percentile' the 97 Percentile of column 'Val_1' for the ID '235',

For the column 'Col2_50_Percentile' the 50 Percentile of column 'Val_2' for the ID '235',

For the column 'Col2_97_Percentile' the 97 Percentile of column 'Val_2' for the ID '235',

For the column 'Col1_50_Percentile' the 50 Percentile of column 'Val_1' for the ID '237',

For the column 'Col1_97_Percentile' the 97 Percentile of column 'Val_1' for the ID '237',

For the column 'Col2_50_Percentile' the 50 Percentile of column 'Val_2' for the ID '237',

For the column 'Col2_97_Percentile' the 97 Percentile of column 'Val_2' for the ID '237'

and so on..

Of course for the statistical Analyse there are more datas in the table.

Regards

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Here's an article by Dwain Camps in which he explains how to use percentiles in versions previous to 2012. https://www.simple-talk.com/sql/t-sql-programming/using-the-t-sql-percentile-analytic-functions-in-sql-server-2000,-2005-and-2008/

If anyone wants to give it a shot, here's the sample data in consumable format and the code for 2012 (which won't solve the problem but can give expected results).

CREATE TABLE #PercTest(

ID int,

Val_1 int,

Val_2 int

);

INSERT INTO #PercTest

VALUES

(235,10,15),

(235,15,11),

(235,23,23),

(237,17,17),

(237,23,1 ),

(237,25,12),

(238,34,25),

(238,25,24),

(238,25,21);

SELECT DISTINCT

ID,

PERCENTILE_DISC(.50) WITHIN GROUP(ORDER BY Val_1) OVER(PARTITION BY ID) Val1_Perc_50,

PERCENTILE_DISC(.975) WITHIN GROUP(ORDER BY Val_1) OVER(PARTITION BY ID) [Val1_Perc_97.5],

PERCENTILE_DISC(.50) WITHIN GROUP(ORDER BY Val_2) OVER(PARTITION BY ID) Val2_Perc_50,

PERCENTILE_DISC(.975) WITHIN GROUP(ORDER BY Val_2) OVER(PARTITION BY ID) [Val2_Perc_97.5]

FROM #PercTest;

GO

DROP TABLE #PercTest;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

glarry Mr or Mrs. 500 Points: 572 More actions · Answer 4

Thanks for the answer, i know in SQL Server 2012 there are the new functions for calculate percentile, but I still using 2008.

I read the article by Dwain Camps before, but it's made for one column of ID and one column of values only for one percentile.

Regards