Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server inter-quartile range function


IQR

Updated: 28 February 2011


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.
Syntax
XLeratorDB syntax for IQR Inter-Quartile-Range function for SQL Server
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.
@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.
Return Types
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.
·         Available in XLeratorDB / statistics 2008 only
Examples
In this example, we calculate the inter-qaurtile 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.
 Inter-Quartile Range
----------------------
               77849.5
 
(1 row(s) affected)


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.
city           Inter-Quartile Range
------------ ----------------------
Atlanta                       90387
Boston                     96234.25
Chicago                       51950
Dallas                     98678.75
Denver                        91274
Los Angeles                68358.75
Miami                       70158.5
New York                    62021.5
Phoenix                     87644.5
San Franciso              106344.75
 
(10 row(s) affected)


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.
 
city           Inter-Quartile Range
------------ ----------------------
Chicago                     46132.5
Los Angeles                54504.75
Miami                       55610.5
New York                      50121
Phoenix                    65504.75
 
(5 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.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.
dsc    Inter-Quartile Range
---- ----------------------
ABC                      20
DEF                      12
GHI                      10
JKL                       0
 

(4 row(s) affected)



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service