Percentile Calculation

Hi All,

I have around 1000 employee records containing employee number and their salary. How do i calculate percentile on these records?

For example
How to calculate 25th Percentile on Salary of these 1000 records?

Hope I am clear with my question.

Thanks in advance.

Regards,
qALook up the "TOP n [PERCENT]" clause in Books Online.

select top 25 percent * from [YourTable]|||Hi,
Thanks for your immediate response. But you got it wrong. Its not Percentage, its PERCENTILE. If you have used MS-Excel there is a function called
PERCENTILE(Array, k)

For example if I have 10,20,30,40,50 as values
then 30th PERCENTILE of this comes as 22

Thanks
qA|||No, I don't get how the calculation is done. And Excel's Books Online (which is normally very good at describing statistical functions) gives no explanation of the algorithm at all.|||Wikipedia, while citing Excel's function as an example, appears to me to be describing behavior similiar to TOP %:

http://en.wikipedia.org/wiki/Percentile|||Hi,
The top clause in a query will return one ore more than one records. But Percentile will always return one and only one value.|||Yes, but how to calculate that value? I found this lovely quote:

"There is no universally accepted definition of a percentile."

on this website: http://cnx.rice.edu/content/m10805/latest/

Very curious. I guess what intrigues me is that I have been using Excel and doing applied business statistics and data mining for more than a decade and have not run into this, or ever used that Excel function before.

Try describing what you want to do, without referring to the Excel function.|||OK, the more I research PERCENTILE calculation, the more I understand why I've never used it. It is about the stupidest statistical measure I have ever seen. Why? Because makes a strict linear interpolation between just two data points out of the set, without any regard to the distribution of the data.

As an example, the 30th percentile of your values (10, 20, 30, 40, 50) is 22, as Excel calculates it. But the 30th percentile of the following values (0, 10, 20, 1000, 1000000) is...guess what...also 22.

I see no practical purpose for such a measure unless your goal is to mislead your audience.

percentile and PercentRank

Hello All,

Does anyone know how to do an expression that calculate percentrank and percentiles? I have a table with a list of transactions and their response time. I need to find the 75th, 90th, 95th, and 98th percentile as well as the percentrank of <=5 seconds, 10 seconds, 30 seconds and 60 seconds.

Any help is appreciated!

Those are all SQL functions, not SSRS ones

NTILE(N) can give you the percentiles

so NTILE(100) will give you 1-100, while NTILE(10) is 1-10

SQL Ranking Functions - RANK, NTILE, DENSE_RANK, ROW_NUMBER

http://msdn2.microsoft.com/en-gb/library/ms189798(SQL.90).aspx

|||

Hmmmm. So how do I do it in Reporting Services? Do I need to put the NTILE function in my base SQL statement? Or are you saying the Microsoft does not have the ability to percent rank and percentiles in SSRS? I am starting to find it odd that Excel creates better reports than SSRS.

Thanks for your help

|||

yes, you use it in the base SQL statement in the "Dataset" tab (or Stored Proc)

should be much faster too

I haven't explored whether ranking is available in SSRS, but knowing Excel doesn't have ranking function either, I will assume minimum from SSRS

Note: if you have filter or parameterized report, ranking is RELATIVE in the "selected" dataset, not the "whole" dataset

e.g. if you pick only 1 person, his/her rank will always be #1; when you pick 10 person, his/her rank may be #1 to #10

|||

A query that uses the ROW_NUMBER() function might look something like:

Code Snippet

declare @.tranTable table
( tranID varchar(20),
elapsedTime integer
)
insert into @.tranTable
select 'Transaction # 1', 37.1403174930829 union all
select 'Transaction # 2', 26.0536172199012 union all
select 'Transaction # 3', 36.928245609864 union all
select 'Transaction # 4', 25.9599151436726 union all
select 'Transaction # 5', 57.9377841191627 union all
select 'Transaction # 6', 62.0332552986104 union all
select 'Transaction # 7', 42.2948297227879
--select * from @.tranTable

;with maxRank as
( select count(*) as maxRank
from @.tranTable
), ranker as
( select TranId,
elapsedTime,
100.0 * row_number() over
( order by elapsedTime, tranId ) / maxRank
as percentile
from maxRank
cross join @.tranTable
)
select * from ranker

With results that look something like:

Code Snippet

TranId elapsedTime percentile
-- --
Transaction # 4 25 14.285714285714
Transaction # 2 26 28.571428571428
Transaction # 3 36 42.857142857142
Transaction # 1 37 57.142857142857
Transaction # 7 42 71.428571428571
Transaction # 5 57 85.714285714285
Transaction # 6 62 100.000000000000

|||Excel does have both a PERCENTRANK and PERCENTILE functions which are very valuable. For example if you want to to find the 90th Percentile you would have ...

Code Snippet

=PERCENTILE(AF26:AF34,0.9)

So I would think SQL would have something like

Code Snippet

=PERCENTILE (fieldnamehere,0.9)

But it doesnt. Likewise with percentrenk, in excel you would have

Code Snippet

=PERCENTRANK(AF26:AF34,25.5,4)

But with either, SSRS or a SQL query does not appear to have a function. Still baffling to me. They do provide a standard deviation, SUM, AVG, and other basic reporting functions.

|||but no PRODUCT() aggregate

Percentile

Hi, l would like to calculate 80% and 95% percentile of a column from my table in SQL server 2005. Does SQL server 2005 provides such built-in function, if not, how should l do? Any suggestion? (Suggest to MS : Please include percentile as a built-in function on the next services pack). Thanks and happy labor day!

Lookup the NTILE and RANK functions in Books Online.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

|||

Can you provide more info about what percentage you want to calculate and what values you have available?

|||

Hi,

Thanks for the reply.

Currently l've a table with the following table structure, i.e. Region, School Name and Scope. l am interested to know the 95% and 80% percentile of the Scope in each region. How should l do?

|||

Without DDL and sample data this is difficult to answer, however
this may help

select Region,
[School Name],
Scope
from (select Region,
[School Name],
Scope,
NTILE(100) OVER(PARTITION BY Region ORDER BY Scope) AS NTile100
from RegionTable) X
where NTile100 >= 95

|||

Hi, Mark

l've a table called RESULT with the structure, i.e.

Region VARCHAR(50)

School Name VARCHAR(50)

Score FLOAT

With Sample Data

Region School Score

A X 75

A X 90

A X 85

A Y 60

A Y 30

B W 90

........................

So l would interest to know the 95% & 85% score for each region. At the end, for each percentile, l should have one score corresponding to it, i.e For region A, my 95% percentile is 80 and 80% oercentile is 70.

By using your method, l will have a few record with percentile 95%.....so l wonder which one should l pick?

Again, thanks a lot for your help

|||

Hi,

Currently l'm adopt the following solution to the problem, i.e.

SELECT region,

(SELECT MAX(score)

FROM (

SELECT TOP(95) score FROM Result

WHERE region = s.region

ORDER BY score

) As Tmp) as score95

FROM Result s

GROUP BY region

l am looking for something simple to find out the score represent 95% percentile.......someone mentions to me that by it can be done by using NTILE(100)....but l dun have any idea how do to that.

Percentage Expression

Hi,
How do I calculate a % for the Group Totals ?
%age = ((CurrentYear - PreviousYear) / PreviousYear)
Thankstry sum( ((CurrentYear - PreviousYear) / PreviousYear) )|||If you want the percentage for the entire group, I'd do it as %age =(sum(current year) -sum(previous year))/sum(previous year).
if you do %age = sum((current - previous)/previous) you'll get the sum
of all the individual percentages|||If you want the percentage for the entire group, I'd do it as %age =(sum(current year) -sum(previous year))/sum(previous year).
if you do %age = sum((current - previous)/previous) you'll get the sum
of all the individual percentages|||If you want the percentage for the entire group, I'd do it as %age =(sum(current year) -sum(previous year))/sum(previous year).
if you do %age = sum((current - previous)/previous) you'll get the sum
of all the individual percentages

Percentage Difference Calculation!

I have a table as follows:
Year Month Value %Diff
2004 Jul 200
Aug 100
etc etc
2003 Jul 300
Aug 400
etc etc
I need to be able to calculate the % difference for each month compared to
the same month for the previous year. The Previous() function is close to
what I need but not close enough. Any help would be much appreciated?If you have control over the source query, a LEFT OUTER JOIN ... ON
a.Month=b.Month and a.Year=b.Year-1 would bring back the data you need on
each row to calculate the diff. As you can see from this post and others,
my philosophy is not to jump through major coding hoops in the report if you
can easily solve it in the SQL query.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"St Matthew" <StMatthew@.discussions.microsoft.com> wrote in message
news:676C6EB0-0383-4BE6-86E5-58DFF89AA30B@.microsoft.com...
>I have a table as follows:
> Year Month Value %Diff
> 2004 Jul 200
> Aug 100
> etc etc
> 2003 Jul 300
> Aug 400
> etc etc
> I need to be able to calculate the % difference for each month compared to
> the same month for the previous year. The Previous() function is close to
> what I need but not close enough. Any help would be much appreciated?|||I should have mentioned in my SQL examples below that a and b are aliases
for the same table.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:OZdTrAb0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> If you have control over the source query, a LEFT OUTER JOIN ... ON
> a.Month=b.Month and a.Year=b.Year-1 would bring back the data you need on
> each row to calculate the diff. As you can see from this post and others,
> my philosophy is not to jump through major coding hoops in the report if
> you can easily solve it in the SQL query.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "St Matthew" <StMatthew@.discussions.microsoft.com> wrote in message
> news:676C6EB0-0383-4BE6-86E5-58DFF89AA30B@.microsoft.com...
>>I have a table as follows:
>> Year Month Value %Diff
>> 2004 Jul 200
>> Aug 100
>> etc etc
>> 2003 Jul 300
>> Aug 400
>> etc etc
>> I need to be able to calculate the % difference for each month compared
>> to
>> the same month for the previous year. The Previous() function is close
>> to
>> what I need but not close enough. Any help would be much appreciated?
>

Percentage Calulation in RS

Hey all,
I need to calculate % bases on 2 columns (Col1 and Col2), and my result that
I want is in Col3(%). Please see the layout below. Is that possible?
Col1 Col2 Col3(%)
--
A 5 50%
B 2 20%
C 3 30%
D 0 0%
Total 10 100%
Thanks in advance!
Calculate Percentage> Col1 Col2 Col3(%)
> --
> A 5 (textbox2) 50%
> B 2 (textbox2) 20%
> C 3 (textbox2) 30%
> D 0 (textbox2) 0%
> Total 10(textbox34) 100%
Assuming you have Tabular report Design,
Expression in Col3 can be -
= Reportitems!textbox2.value/Reportitems!textbox34.value
and Format field with %
(My formula syntax can be wrong, use textbox reading syntax)