PERCENTILE
Updated: 28 February 2011
Note: This documentation is for the SQL2008 (and later) version of this XLeratorDB function, it is not compatible with SQL Server 2005.
Click here for the SQL2005 version of the PERCENTILE function
Use the aggregate function PERCENTILE to calculate the kth percentile of value in a dataset. To calculate the kth percentile of a dataset, put all the non-NULL values into an ascending ordered set P. Set N equal to the number of items in P.
Syntax
Arguments
@x
the input values for the function. @ x is an expression of type float or of a type that can be implicitly converted to float.
@k
is the percentile value in the range 0 through 1, inclusive. @k is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
· If @k <0 or @k > 1 one, PERCENTILE returns an error.
· If @k is not a multiple of 1/(n-1), PERCENTILE interpolates to determine the value at the kth percentile.
· If @k = 0, the smallest value in the group is returned.
· If @k = 1, the largest value in the group is returned.
· This is the equivalent of the PERCENTILE and the PERCENTILE.INC function in EXCEL. For the PERCENTILE.EXC function, see PERCENTILE_EXC.
· @k must remain invariant for a group.
· PERCENTILE is an aggregate function and follows the same conventions as all other aggregate functions in SQL Server.
· If you have previously used the PERCENTILE scalar function, the PERCENTILE aggregate has a different syntax. The PERCENTILE scalar function is no longer available in XLeratorDB/statistics2008, though you can still use the scalar PERCENTILE_q
· If there is only one value in the dataset, that value will be returned by the function.
· NULL values are not included.
Examples
In this example, we calculate the 90th percentile for selected salary inforrmation collected from 10 cities.
SELECT wct.PERCENTILE(
salary --@x
,.90 --@k
) as PERCENTILE
FROM (VALUES
('New York','429-00-6486',236503),
('New York','90-70-2526',224472),
('New York','87-85-0404',139802),
('New York','716-89-3089',185287),
('New York','159-78-5370',211900),
('New York','195-97-6820',186703),
('New York','95-49-2813',167451),
('New York','37-20-7422',149462),
('New York','44-48-0076',214708),
('New York','514-79-0041',226485),
('Los Angeles','526-34-4521',196402),
('Los Angeles','800-50-0868',205359),
('Los Angeles','41-34-3618',195679),
('Los Angeles','854-29-9398',131925),
('Los Angeles','673-30-3623',171091),
('Los Angeles','537-58-8889',110217),
('Los Angeles','808-68-4234',192836),
('Los Angeles','359-81-6735',209346),
('Los Angeles','731-80-2303',182186),
('Los Angeles','214-58-0842',125355),
('Chicago','456-79-9682',183698),
('Chicago','807-97-4784',194282),
('Chicago','981-16-3724',156083),
('Chicago','252-34-3054',226619),
('Chicago','613-28-9452',153366),
('Chicago','785-25-8628',205709),
('Chicago','451-26-7350',206085),
('Chicago','443-94-2401',120587),
('Chicago','696-26-8113',171185),
('Chicago','277-31-9760',211160),
('Dallas','537-88-7532',245231),
('Dallas','393-25-3503',238733),
('Dallas','612-17-0712',103152),
('Dallas','384-93-7285',228842),
('Dallas','745-10-7587',154749),
('Dallas','950-20-4045',102156),
('Dallas','477-48-7550',196533),
('Dallas','427-52-8597',238970),
('Dallas','891-19-0810',245204),
('Dallas','564-79-7612',196946),
('Boston','216-84-7134',128035),
('Boston','515-84-4073',249093),
('Boston','92-43-5775',205026),
('Boston','144-08-1092',198120),
('Boston','469-14-5012',174143),
('Boston','379-92-8313',166215),
('Boston','71-22-5132',105058),
('Boston','257-39-0324',107247),
('Boston','611-57-4279',118561),
('Boston','956-53-2865',232789),
('Denver','711-81-0072',240720),
('Denver','673-39-5028',159706),
('Denver','554-33-3980',232493),
('Denver','770-03-5304',203310),
('Denver','732-47-5077',123106),
('Denver','573-18-3567',149999),
('Denver','93-92-0334',162657),
('Denver','424-76-1468',116322),
('Denver','403-47-0063',246058),
('Denver','460-58-1833',198043),
('Miami','221-04-4153',130962),
('Miami','179-09-9839',227246),
('Miami','564-76-9437',144027),
('Miami','407-48-4081',138549),
('Miami','526-79-1840',208006),
('Miami','72-68-4977',170109),
('Miami','235-72-3903',191669),
('Miami','436-62-0474',161164),
('Miami','430-52-3914',162507),
('Miami','459-27-5541',238972),
('Phoenix','576-38-4531',238281),
('Phoenix','65-64-1278',197678),
('Phoenix','880-29-1997',159183),
('Phoenix','304-72-1881',194733),
('Phoenix','61-20-1046',221045),
('Phoenix','64-95-5514',105577),
('Phoenix','262-63-4021',186399),
('Phoenix','661-84-1023',234974),
('Phoenix','892-31-4821',115076),
('Phoenix','319-91-9463',239548),
('San Franciso','136-67-6873',148829),
('San Franciso','5-41-7374',114161),
('San Franciso','381-26-8852',232509),
('San Franciso','620-64-6243',112686),
('San Franciso','128-33-5550',208679),
('San Franciso','422-00-0156',107685),
('San Franciso','370-98-5607',133224),
('San Franciso','91-58-9543',218955),
('San Franciso','911-35-0448',187826),
('San Franciso','734-65-1268',223683),
('Atlanta','334-97-0585',240384),
('Atlanta','405-12-4222',124350),
('Atlanta','43-05-7567',233836),
('Atlanta','882-97-7996',134091),
('Atlanta','368-91-4292',173787),
('Atlanta','408-04-5921',140769),
('Atlanta','232-13-5280',206307),
('Atlanta','88-41-2584',118159),
('Atlanta','539-03-7548',116718),
('Atlanta','587-63-6935',174801)
)p(city, id, salary)
This produces the following result.
PERCENTILE
----------------------
238326.2
(1 row(s) affected)
In this example we will calculate the 90th percentile in each city.
SELECT city,
wct.PERCENTILE(salary, .90) as PERCENTILE
FROM (VALUES
('New York','429-00-6486',236503),
('New York','90-70-2526',224472),
('New York','87-85-0404',139802),
('New York','716-89-3089',185287),
('New York','159-78-5370',211900),
('New York','195-97-6820',186703),
('New York','95-49-2813',167451),
('New York','37-20-7422',149462),
('New York','44-48-0076',214708),
('New York','514-79-0041',226485),
('Los Angeles','526-34-4521',196402),
('Los Angeles','800-50-0868',205359),
('Los Angeles','41-34-3618',195679),
('Los Angeles','854-29-9398',131925),
('Los Angeles','673-30-3623',171091),
('Los Angeles','537-58-8889',110217),
('Los Angeles','808-68-4234',192836),
('Los Angeles','359-81-6735',209346),
('Los Angeles','731-80-2303',182186),
('Los Angeles','214-58-0842',125355),
('Chicago','456-79-9682',183698),
('Chicago','807-97-4784',194282),
('Chicago','981-16-3724',156083),
('Chicago','252-34-3054',226619),
('Chicago','613-28-9452',153366),
('Chicago','785-25-8628',205709),
('Chicago','451-26-7350',206085),
('Chicago','443-94-2401',120587),
('Chicago','696-26-8113',171185),
('Chicago','277-31-9760',211160),
('Dallas','537-88-7532',245231),
('Dallas','393-25-3503',238733),
('Dallas','612-17-0712',103152),
('Dallas','384-93-7285',228842),
('Dallas','745-10-7587',154749),
('Dallas','950-20-4045',102156),
('Dallas','477-48-7550',196533),
('Dallas','427-52-8597',238970),
('Dallas','891-19-0810',245204),
('Dallas','564-79-7612',196946),
('Boston','216-84-7134',128035),
('Boston','515-84-4073',249093),
('Boston','92-43-5775',205026),
('Boston','144-08-1092',198120),
('Boston','469-14-5012',174143),
('Boston','379-92-8313',166215),
('Boston','71-22-5132',105058),
('Boston','257-39-0324',107247),
('Boston','611-57-4279',118561),
('Boston','956-53-2865',232789),
('Denver','711-81-0072',240720),
('Denver','673-39-5028',159706),
('Denver','554-33-3980',232493),
('Denver','770-03-5304',203310),
('Denver','732-47-5077',123106),
('Denver','573-18-3567',149999),
('Denver','93-92-0334',162657),
('Denver','424-76-1468',116322),
('Denver','403-47-0063',246058),
('Denver','460-58-1833',198043),
('Miami','221-04-4153',130962),
('Miami','179-09-9839',227246),
('Miami','564-76-9437',144027),
('Miami','407-48-4081',138549),
('Miami','526-79-1840',208006),
('Miami','72-68-4977',170109),
('Miami','235-72-3903',191669),
('Miami','436-62-0474',161164),
('Miami','430-52-3914',162507),
('Miami','459-27-5541',238972),
('Phoenix','576-38-4531',238281),
('Phoenix','65-64-1278',197678),
('Phoenix','880-29-1997',159183),
('Phoenix','304-72-1881',194733),
('Phoenix','61-20-1046',221045),
('Phoenix','64-95-5514',105577),
('Phoenix','262-63-4021',186399),
('Phoenix','661-84-1023',234974),
('Phoenix','892-31-4821',115076),
('Phoenix','319-91-9463',239548),
('San Franciso','136-67-6873',148829),
('San Franciso','5-41-7374',114161),
('San Franciso','381-26-8852',232509),
('San Franciso','620-64-6243',112686),
('San Franciso','128-33-5550',208679),
('San Franciso','422-00-0156',107685),
('San Franciso','370-98-5607',133224),
('San Franciso','91-58-9543',218955),
('San Franciso','911-35-0448',187826),
('San Franciso','734-65-1268',223683),
('Atlanta','334-97-0585',240384),
('Atlanta','405-12-4222',124350),
('Atlanta','43-05-7567',233836),
('Atlanta','882-97-7996',134091),
('Atlanta','368-91-4292',173787),
('Atlanta','408-04-5921',140769),
('Atlanta','232-13-5280',206307),
('Atlanta','88-41-2584',118159),
('Atlanta','539-03-7548',116718),
('Atlanta','587-63-6935',174801)
)p(city, id, salary)
GROUP BY city
This produces the following result.
city PERCENTILE
------------ ----------------------
Atlanta 234490.8
Boston 234419.4
Chicago 212705.9
Dallas 245206.7
Denver 241253.8
Los Angeles 205757.7
Miami 228418.6
New York 227486.8
Phoenix 238407.7
San Franciso 224565.6
(10 row(s) affected)
In this example, we only select those cities where the 90th percentile is less than 225,000.
SELECT city,
wct.PERCENTILE(salary, .90) as PERCENTILE
FROM (VALUES
('New York','429-00-6486',236503),
('New York','90-70-2526',224472),
('New York','87-85-0404',139802),
('New York','716-89-3089',185287),
('New York','159-78-5370',211900),
('New York','195-97-6820',186703),
('New York','95-49-2813',167451),
('New York','37-20-7422',149462),
('New York','44-48-0076',214708),
('New York','514-79-0041',226485),
('Los Angeles','526-34-4521',196402),
('Los Angeles','800-50-0868',205359),
('Los Angeles','41-34-3618',195679),
('Los Angeles','854-29-9398',131925),
('Los Angeles','673-30-3623',171091),
('Los Angeles','537-58-8889',110217),
('Los Angeles','808-68-4234',192836),
('Los Angeles','359-81-6735',209346),
('Los Angeles','731-80-2303',182186),
('Los Angeles','214-58-0842',125355),
('Chicago','456-79-9682',183698),
('Chicago','807-97-4784',194282),
('Chicago','981-16-3724',156083),
('Chicago','252-34-3054',226619),
('Chicago','613-28-9452',153366),
('Chicago','785-25-8628',205709),
('Chicago','451-26-7350',206085),
('Chicago','443-94-2401',120587),
('Chicago','696-26-8113',171185),
('Chicago','277-31-9760',211160),
('Dallas','537-88-7532',245231),
('Dallas','393-25-3503',238733),
('Dallas','612-17-0712',103152),
('Dallas','384-93-7285',228842),
('Dallas','745-10-7587',154749),
('Dallas','950-20-4045',102156),
('Dallas','477-48-7550',196533),
('Dallas','427-52-8597',238970),
('Dallas','891-19-0810',245204),
('Dallas','564-79-7612',196946),
('Boston','216-84-7134',128035),
('Boston','515-84-4073',249093),
('Boston','92-43-5775',205026),
('Boston','144-08-1092',198120),
('Boston','469-14-5012',174143),
('Boston','379-92-8313',166215),
('Boston','71-22-5132',105058),
('Boston','257-39-0324',107247),
('Boston','611-57-4279',118561),
('Boston','956-53-2865',232789),
('Denver','711-81-0072',240720),
('Denver','673-39-5028',159706),
('Denver','554-33-3980',232493),
('Denver','770-03-5304',203310),
('Denver','732-47-5077',123106),
('Denver','573-18-3567',149999),
('Denver','93-92-0334',162657),
('Denver','424-76-1468',116322),
('Denver','403-47-0063',246058),
('Denver','460-58-1833',198043),
('Miami','221-04-4153',130962),
('Miami','179-09-9839',227246),
('Miami','564-76-9437',144027),
('Miami','407-48-4081',138549),
('Miami','526-79-1840',208006),
('Miami','72-68-4977',170109),
('Miami','235-72-3903',191669),
('Miami','436-62-0474',161164),
('Miami','430-52-3914',162507),
('Miami','459-27-5541',238972),
('Phoenix','576-38-4531',238281),
('Phoenix','65-64-1278',197678),
('Phoenix','880-29-1997',159183),
('Phoenix','304-72-1881',194733),
('Phoenix','61-20-1046',221045),
('Phoenix','64-95-5514',105577),
('Phoenix','262-63-4021',186399),
('Phoenix','661-84-1023',234974),
('Phoenix','892-31-4821',115076),
('Phoenix','319-91-9463',239548),
('San Franciso','136-67-6873',148829),
('San Franciso','5-41-7374',114161),
('San Franciso','381-26-8852',232509),
('San Franciso','620-64-6243',112686),
('San Franciso','128-33-5550',208679),
('San Franciso','422-00-0156',107685),
('San Franciso','370-98-5607',133224),
('San Franciso','91-58-9543',218955),
('San Franciso','911-35-0448',187826),
('San Franciso','734-65-1268',223683),
('Atlanta','334-97-0585',240384),
('Atlanta','405-12-4222',124350),
('Atlanta','43-05-7567',233836),
('Atlanta','882-97-7996',134091),
('Atlanta','368-91-4292',173787),
('Atlanta','408-04-5921',140769),
('Atlanta','232-13-5280',206307),
('Atlanta','88-41-2584',118159),
('Atlanta','539-03-7548',116718),
('Atlanta','587-63-6935',174801)
)p(city, id, salary)
GROUP BY city
HAVING wct.PERCENTILE(salary, .90) < 225000
This produces the following result.
city PERCENTILE
------------ ----------------------
Chicago 212705.9
Los Angeles 205757.7
San Franciso 224565.6
(3 row(s) affected)
In this example we calculate the 40th percentile for a variety of data groupings, including one NULL value and a GROUP that only contains one member.
SELECT dsc
,wct.PERCENTILE(x, .40) as PERCENTILE
FROM (VALUES
('ABC', 15),('ABC',20),('ABC',35),('ABC',40),('ABC',50),
('DEF', 7),('DEF',10),('DEF',17),('DEF',20),('DEF',25),('DEF',70),
('GHI', 21),('DEF',28),('DEF',NULL),('DEF',38),('DEF',31),('DEF',52),
('JKL', 37)
) p(dsc, x)
GROUP BY dsc
This produces the following result.
dsc PERCENTILE
---- ----------------------
ABC 29
DEF 23
GHI 21
JKL 37
(4 row(s) affected)