Percent Range for a list of numbers

30 Sep 2005 3:19 PM

luvgreen

Hello experts. I need to give a percentile rank for a list of numbers, is
there a SQL server built-in function doing this? Thanks much

Example below:

Data    Rank
13    100.00%
12    88.80%
11    77.70%
8    66.60%
4    55.50%
3    44.40%
2    33.30%
1    0.00%
1    0.00%
1    0.00%

30 Sep 2005 6:46 PM

Jerry Spivey

What is the criteria/formula for calculating these percentages?
Show quote

"luvgreen" <luvgr***@discussions.microsoft.com> wrote in message
news:40C660D3-B447-4F70-AEBE-AC39BFB7898F@microsoft.com...
> Hello experts. I need to give a percentile rank for a list of numbers, is
> there a SQL server built-in function doing this? Thanks much
>
> Example below:
>
> Data Rank
> 13 100.00%
> 12 88.80%
> 11 77.70%
> 8 66.60%
> 4 55.50%
> 3 44.40%
> 2 33.30%
> 1 0.00%
> 1 0.00%
> 1 0.00%
>
>

30 Sep 2005 7:01 PM

Gert-Jan Strik

No there isn't, probably because this operation cannot be performed
without some sort of rollup function with its result passed back to the
main results.

But you can code it like this:

CREATE TABLE MyTable(Data int)
INSERT INTO MyTable VALUES (13)
INSERT INTO MyTable VALUES (12)
INSERT INTO MyTable VALUES (11)
INSERT INTO MyTable VALUES (8)
INSERT INTO MyTable VALUES (4)
INSERT INTO MyTable VALUES (3)
INSERT INTO MyTable VALUES (2)
INSERT INTO MyTable VALUES (1)
INSERT INTO MyTable VALUES (1)
INSERT INTO MyTable VALUES (1)
GO

CREATE FUNCTION dbo.Perc(@number as int,@total as decimal(11,0))
RETURNS varchar(7)
AS
Begin
RETURN CAST(CAST(ROUND((@number*100)/@total,2) AS Decimal(5,2)) AS
varchar(6))+'%'
End
GO

Declare @SUM int
Set @SUM=(SELECT SUM(Data) FROM MyTable)

SELECT Data, dbo.Perc(Data,@SUM)
FROM MyTable
ORDER BY Data DESC
GO

DROP FUNCTION dbo.Perc
DROP TABLE MyTable

Hope this helps,
Gert-Jan

luvgreen wrote:
Show quote

>
> Hello experts. I need to give a percentile rank for a list of numbers, is
> there a SQL server built-in function doing this? Thanks much
>
> Example below:
>
> Data    Rank
> 13      100.00%
> 12      88.80%
> 11      77.70%
> 8       66.60%
> 4       55.50%
> 3       44.40%
> 2       33.30%
> 1       0.00%
> 1       0.00%
> 1       0.00%

30 Sep 2005 8:24 PM

luvgreen

Thank you so very much for helping!! Have a nice weekend.

Show quote

"Gert-Jan Strik" wrote:

> No there isn't, probably because this operation cannot be performed
> without some sort of rollup function with its result passed back to the
> main results.
>
> But you can code it like this:
>
> CREATE TABLE MyTable(Data int)
> INSERT INTO MyTable VALUES (13)
> INSERT INTO MyTable VALUES (12)
> INSERT INTO MyTable VALUES (11)
> INSERT INTO MyTable VALUES (8)
> INSERT INTO MyTable VALUES (4)
> INSERT INTO MyTable VALUES (3)
> INSERT INTO MyTable VALUES (2)
> INSERT INTO MyTable VALUES (1)
> INSERT INTO MyTable VALUES (1)
> INSERT INTO MyTable VALUES (1)
> GO
>
> CREATE FUNCTION dbo.Perc(@number as int,@total as decimal(11,0))
> RETURNS varchar(7)
> AS
> Begin
>   RETURN CAST(CAST(ROUND((@number*100)/@total,2) AS Decimal(5,2)) AS
> varchar(6))+'%'
> End
> GO
>
> Declare @SUM int
> Set @SUM=(SELECT SUM(Data) FROM MyTable)
>
> SELECT Data, dbo.Perc(Data,@SUM)
> FROM MyTable
> ORDER BY Data DESC
> GO
>
> DROP FUNCTION dbo.Perc
> DROP TABLE MyTable
>
> Hope this helps,
> Gert-Jan
>
>
> luvgreen wrote:
> >
> > Hello experts. I need to give a percentile rank for a list of numbers, is
> > there a SQL server built-in function doing this? Thanks much
> >
> > Example below:
> >
> > Data    Rank
> > 13      100.00%
> > 12      88.80%
> > 11      77.70%
> > 8       66.60%
> > 4       55.50%
> > 3       44.40%
> > 2       33.30%
> > 1       0.00%
> > 1       0.00%
> > 1       0.00%
>