IQR

Updated 2023-10-23 20:10:52.723000

Syntax

SELECT [westclintech].[wct].[IQR] (
  <@x, float,>
 ,<@mthd, nvarchar(4000),>)

Description

Use the aggregate function IQR to calculate the inter-quartile range for a dataset. The inter-quartile range is the difference in value between the 1st quartile and the 3rd quartile.

Arguments

@mthd

the quartile calculation method. Use 'INC' for the inclusive method and 'EXC' for the exclusive method. For more information about the two methods, see the PERCENTILE and the PERCENTILE_EXC documentation.

@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.

Return Type

float

Remarks

If @mthd not NULL, 'INC', or 'EXC', IQR returns an error. See PERCENTILE and PERCENTILE_EXC for an explanation of the difference between the inclusive (INC) and exclusive (EXC) methods.

@mthd must remain invariant for a group.

IQR does not include NULL values.

IQR 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, then the inter-quartile range is zero.

Examples

In this example, we calculate the inter-quartile range for selected salary information collected from 10 cities.

SELECT wct.IQR(salary, NULL) as [Inter-Quartile Range]

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":"Inter-Quartile Range","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Inter-Quartile Range":"77849.5"}]}

In this example we will calculate the inter-quartile range in each city.

SELECT city,

       wct.IQR(salary, 'EXC') as [Inter-Quartile Range]

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":"Inter-Quartile Range","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"city":"Atlanta","Inter-Quartile Range":"90387"},{"city":"Boston","Inter-Quartile Range":"96234.25"},{"city":"Chicago","Inter-Quartile Range":"51950"},{"city":"Dallas","Inter-Quartile Range":"98678.75"},{"city":"Denver","Inter-Quartile Range":"91274"},{"city":"Los Angeles","Inter-Quartile Range":"68358.75"},{"city":"Miami","Inter-Quartile Range":"70158.5"},{"city":"New York","Inter-Quartile Range":"62021.5"},{"city":"Phoenix","Inter-Quartile Range":"87644.5"},{"city":"San Franciso","Inter-Quartile Range":"106344.75"}]}

In this example, we only select those cities where the inter-quartile range, using the inclusive method, is less than 70,000.

SELECT city,

       wct.IQR(salary, 'INC') as [Inter-Quartile Range]

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.IQR(salary, 'INC') < 70000;

This produces the following result.

{"columns":[{"field":"city"},{"field":"Inter-Quartile Range","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"city":"Chicago","Inter-Quartile Range":"46132.5"},{"city":"Los Angeles","Inter-Quartile Range":"54504.75"},{"city":"Miami","Inter-Quartile Range":"55610.5"},{"city":"New York","Inter-Quartile Range":"50121"},{"city":"Phoenix","Inter-Quartile Range":"65504.75"}]}

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.IQR(x, 'INC') as [Inter-Quartile Range]

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),

        ('GHI', 28),

        ('GHI', NULL),

        ('GHI', 38),

        ('GHI', 31),

        ('GHI', 52),

        ('JKL', 37)

) p (dsc, x)

GROUP BY dsc;

This produces the following result.

{"columns":[{"field":"dsc"},{"field":"Inter-Quartile Range","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"dsc":"ABC","Inter-Quartile Range":"20"},{"dsc":"DEF","Inter-Quartile Range":"12"},{"dsc":"GHI","Inter-Quartile Range":"10"},{"dsc":"JKL","Inter-Quartile Range":"0"}]}