Logo
Getting started

QUARTILE_EXC

Updated 2023-10-24 13:36:54.290000

Syntax

SELECT [westclintech].[wct].[QUARTILE_EXC] (
  <@x, float,> 
 ,<@k, int,>)

Description

Use the aggregate function QUARTILE_EXC to return the quartile of a dataset. QUARTILE_EXC is equal to PERCENTILE_EXC(x, k*.25). For more information on how PERCENTILE_EXC is calculated, go to the PERCENTILE_EXC 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

t he 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_EXC returns an error.

If @k = 2, then QUARTILE_EXC returns the MEDIAN of the dataset.

If @k = 0, then QUARTILE_EXC returns NULL.

If @k = 4, then QUARTILE _EXC returns NULL.

If @k between 1 and 3, then QUARTILE_EXC returns the PERCENTILE_EXC of the dataset for @k * 0.25.

@K must remain invariant for a group.

QUARTILE_EXC is an aggregate function and follows the same conventions as all other aggregate functions in SQL Server.

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_EXC(   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":"217893.25"}]}

In this example we will calculate the 3rd quartile in each city.

SELECT city,

       wct.QUARTILE_EXC(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":"213189.25"},{"city":"Boston","QUARTILE":"211966.75"},{"city":"Chicago","QUARTILE":"207353.75"},{"city":"Dallas","QUARTILE":"240528.5"},{"city":"Denver","QUARTILE":"234549.75"},{"city":"Los Angeles","QUARTILE":"198641.25"},{"city":"Miami","QUARTILE":"212816"},{"city":"New York","QUARTILE":"224975.25"},{"city":"Phoenix","QUARTILE":"235800.75"},{"city":"San Franciso","QUARTILE":"220137"}]}

In this example, we will select all the cities where the 3rd quartile is greater than 225,000.

SELECT city,

       wct.QUARTILE_EXC(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_EXC(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_EXC(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":"17.5"},{"dsc":"DEF","QUARTILE":"15.25"},{"dsc":"GHI","QUARTILE":"21"},{"dsc":"JKL","QUARTILE":"37"}]}