percentile in SQL Server 2005

Question

Post reply

percentile in SQL Server 2005

dakshinamurthy-655138

SSCertifiable

Points: 5172
More actions
January 24, 2009 at 2:55 am

#196468

Hi,
I would like to find the percentile for .95 and .75 in sql server 2005
when i tried the below query it differs from the percentile value which i got in excel. Please let me know how to find the exact value.
DECLARE @blood TABLE(
systolic int
)
INSERT INTO @blood
SELECT 120
UNION ALL SELECT 125
UNION ALL SELECT 125
UNION ALL SELECT 145
UNION ALL SELECT 145
UNION ALL SELECT 150
UNION ALL SELECT 150
UNION ALL SELECT 160
UNION ALL SELECT 170
UNION ALL SELECT 175
DECLARE @rowcount int
DECLARE @rank25 tinyint
DECLARE @rank75 tinyint
SET @rowcount = (SELECT COUNT(*) from @blood)
--Select @rowcount as rc
SET @rank25 = ROUND(.95*(@rowcount+1),0)
SET @rank75 = ROUND(.75*(@rowcount+1),0)
--SET @rank75 = floor(.95*(@rowcount+1))
SELECT TOP 1 systolic FROM (
SELECT TOP (@rank25) systolic FROM @blood ORDER BY systolic
) b
ORDER BY b.systolic DESC
SELECT TOP 1 systolic FROM (
SELECT TOP (@rank75) systolic FROM @blood ORDER BY systolic
) b
ORDER BY b.systolic DESC
--172.75 for 0.95 found in excel
--157.5 for .75 found in excel
--175 for 0.95 found in sql with above query
--160 for .75 found in sql with above query
with regards
Dakshina Murthy

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Don't use any type of "INT" if you want decimal answers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

Excel automatically adjusts data types and precision to handle different values unless you set the number of decimals. SQL Server doesn't act like that. You'll get implicit conversions that might not result in what you want.

As Jeff mentioned, for these calculations, set all values to specific decimal data types, and then use decimals in your calculations.

SELECT 120.00

UNION ALL SELECT 125.00

..

and get the rowcount, set it to a decimal value, multiply and divide by decimals, etc.

dakshinamurthy-655138 SSCertifiable Points: 5172 More actions · Answer 3

Hi,

Even after using rowcount , it is having no difference.

--172.75 for 0.95 found in excel

--157.5 for .75 found in excel

--175 for 0.95 found in sql with above query

--160 for .75 found in sql with above query

With Regards

Dakshina Murthy

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Go back and look at my previous post... problem is NOT in the rowcount... it's how you're doing the math. Integer math will only yield integers and you need to change that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

suthan.na SSC Journeyman Points: 83 More actions · Answer 5

I realize that this was a post from long time ago, but thought I will post the answer to bring this to a logical conclusion. I modified the script a bit and added more logic; now the results are matching with what Excel gives -

CREATE TABLE #blood_values

(

rank_id INT IDENTITY(1, 1),

systolic INT

)

INSERT INTO #blood_values

(systolic)

SELECT 120

UNION ALL

SELECT 125

UNION ALL

SELECT 125

UNION ALL

SELECT 145

UNION ALL

SELECT 145

UNION ALL

SELECT 150

UNION ALL

SELECT 150

UNION ALL

SELECT 160

UNION ALL

SELECT 170

UNION ALL

SELECT 175

DECLARE @rowcount INT

DECLARE @percentile_position FLOAT

DECLARE @percentile_position_int INT

DECLARE @percentile_position_decimal FLOAT

DECLARE @pp_value INT

DECLARE @pp_prev_value INT

SET @rowcount = (SELECT COUNT(*)

FROM #blood_values)

-- Replace 0.95 with appropriate value to get the desired percentile

SET @percentile_position = 0.5 * ( @rowcount + 1 )

-- Decimal part will be removed since FLOAT is assigned to an INT

SET @percentile_position_int = round(@percentile_position, 0)

-- Get the decimal part

SET @percentile_position_decimal = abs(@percentile_position - @percentile_position_int)

-- Percentile value

IF @percentile_position_decimal = 0

BEGIN

SELECT systolic

FROM #blood_values

WHERE rank_id = @percentile_position_int

END

ELSE

BEGIN

SELECT @pp_value = systolic

FROM #blood_values

WHERE rank_id = @percentile_position_int

SELECT @pp_prev_value = systolic

FROM #blood_values

WHERE rank_id = @percentile_position_int - 1

SELECT @pp_value - ( ( @pp_value - @pp_prev_value ) * @percentile_position_decimal )

END

prabhu.st SSCrazy Points: 2552 More actions · Answer 6

Hi Suthan

I believe I am looking into this post very late. but it is very very useful for me.. thanks to you..

and Thanks to SQLSERVERCETRAL, because nowhere I got this exact referece, once again this site proved its member's talent.

Thanks,

Prabhu