QUARTILE
Updated 2023-10-24 13:34:09.983000
Syntax
SELECT [westclintech].[wct].[QUARTILE] (
<@x, float,>
,<@k, int,>)
Description
Use the aggregate function QUARTILE to return the quartile of a dataset. QUARTILE is equal to PERCENTILE(x, k*.25). For more information on how PERCENTILE is calculated, go to the PERCENTILE documentation.
Arguments
@x
the values on which the quartile is to be calculated. @x is an expression of type float or of a type that can be implicitly converted to float.
@k
the quartile to be calculated. @k is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
If @k is less than zero or if @k is greater than four, QUARTILE returns an error.
MIN, MEDIAN, and MAX return the same value as QUARTILE when @k is equal to zero, two and 4, respectively.
If @k = 0, then QUARTILE returns the MIN of the dataset.
If @k = 4, then QUARTILE returns the MAX of the dataset.
If @k between 1 and 3, then QUARTILE returns the PERCENTILE of the dataset for @k * 0.25.
@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.
If there is only one value in the dataset, that value will be returned by the function.
Examples
In this example, we calculate the 3rd quartile for selected salary inforrmation collected from 10 cities.
SELECT wct.QUARTILE(
salary --@x
,3 --@k
) as QUARTILE
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.
{"columns":[{"field":"QUARTILE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"QUARTILE":"215769.75"}]}
In this example we will calculate the 3rd quartile in each city.
SELECT city,
wct.QUARTILE(salary, 3) as QUARTILE
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.
{"columns":[{"field":"city"},{"field":"QUARTILE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"city":"Atlanta","QUARTILE":"198430.5"},{"city":"Boston","QUARTILE":"203299.5"},{"city":"Chicago","QUARTILE":"205991"},{"city":"Dallas","QUARTILE":"238910.75"},{"city":"Denver","QUARTILE":"225197.25"},{"city":"Los Angeles","QUARTILE":"196221.25"},{"city":"Miami","QUARTILE":"203921.75"},{"city":"New York","QUARTILE":"222031"},{"city":"Phoenix","QUARTILE":"231491.75"},{"city":"San Franciso","QUARTILE":"216386"}]}
In this example, we will select all the cities where the 3rd quartile is greater than 225,000.
SELECT city,
wct.QUARTILE(salary, 3) as QUARTILE
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.QUARTILE(salary, 3) > 225000;
This produces the following result.
{"columns":[{"field":"city"},{"field":"QUARTILE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"city":"Dallas","QUARTILE":"238910.75"},{"city":"Denver","QUARTILE":"225197.25"},{"city":"Phoenix","QUARTILE":"231491.75"}]}
In this example we calculate the first quartile for a variety of data groupings, including one NULL value and a GROUP that only contains one member.
SELECT dsc,
wct.QUARTILE(x, 1) as QUARTILE
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.
{"columns":[{"field":"dsc"},{"field":"QUARTILE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"dsc":"ABC","QUARTILE":"20"},{"dsc":"DEF","QUARTILE":"17.75"},{"dsc":"GHI","QUARTILE":"21"},{"dsc":"JKL","QUARTILE":"37"}]}