Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server MEDIAN function


MEDIAN

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 MEDIAN function


Use the aggregate function MEDIAN to return the median of the given numbers. The median is the number in the middle of a set of numbers.
Syntax
XLeratorDB syntax for MEDIAN function for SQL Server
Arguments
@Known_x
the known x-values to be used in the MEDIAN calculation. @Known_x is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
·         If there is an even number of numbers in the set, then MEDIAN calculates the average of the two numbers in the middle.
·         MEDIAN does not include NULL values.
·         MEDIAN will return a NULL if there is only one value in the group.
·         MEDIAN is an aggregate function and follows the same conventions as all other AGGREGATE functions in SQL Server.
·         If you have previously used the MEDIAN scalar function, the MEDIAN aggregate has a different syntax. The MEDIAN scalar function is no longer available in XLeratorDB/statistics2008.
Examples
SELECT wct.MEDIAN(x) as MEDIAN
FROM (VALUES
(1),
(2),
(4),
(7),
(8),
(9),
(10),
(12)
) n(x)
 

This produces the following result
                MEDIAN
----------------------
                   7.5
 
(1 row(s) affected)


In this example, we have salary information collected for 10 people in 10 different cities. We can calculate the median salary across the entire population.
SELECT wct.MEDIAN(salary) as MEDIAN
FROM (VALUES
('New York','781-40-1515',14901347),
('New York','282-59-4245',7891086),
('New York','471-76-6363',24082868),
('New York','817-54-9141',6343371),
('New York','604-44-4703',4736104),
('New York','195-53-8949',21626261),
('New York','326-09-8461',19252906),
('New York','592-00-9748',15614953),
('New York','498-95-6935',2263965),
('New York','219-24-4076',7969176),
('Los Angeles','157-79-4345',2545791),
('Los Angeles','840-20-4955',3844384),
('Los Angeles','25-32-7992',7501493),
('Los Angeles','536-60-2483',14150187),
('Los Angeles','730-94-1257',21130745),
('Los Angeles','971-32-9439',3479240),
('Los Angeles','693-55-3375',2428135),
('Los Angeles','44-00-1792',12562931),
('Los Angeles','260-97-4805',6650349),
('Los Angeles','885-70-9272',9926430),
('Chicago','164-72-0542',16107100),
('Chicago','687-97-8248',3794191),
('Chicago','754-78-1002',20529865),
('Chicago','163-56-7721',15674667),
('Chicago','765-24-0541',18766485),
('Chicago','769-33-7458',20106371),
('Chicago','181-31-5887',22686260),
('Chicago','386-42-7266',24437495),
('Chicago','537-03-6551',5513915),
('Chicago','454-31-9749',14760208),
('Dallas','10-15-4091',24277270),
('Dallas','818-17-1808',17138895),
('Dallas','168-96-8253',5038217),
('Dallas','978-11-2294',17251430),
('Dallas','153-86-6611',2865907),
('Dallas','325-06-9889',20413445),
('Dallas','306-24-6442',10423512),
('Dallas','242-34-5411',1318956),
('Dallas','175-98-7791',22356674),
('Dallas','889-65-2818',23469564),
('Boston','684-51-3410',8343599),
('Boston','567-22-2081',22520551),
('Boston','762-35-5534',8020738),
('Boston','673-92-6296',8499708),
('Boston','614-75-9862',14224087),
('Boston','974-01-1606',22539319),
('Boston','818-76-2325',9743214),
('Boston','119-36-0656',14199650),
('Boston','672-56-6167',12755767),
('Boston','485-77-9767',10015356),
('Denver','661-09-5879',5292759),
('Denver','256-03-6426',2031359),
('Denver','328-43-7297',18055202),
('Denver','405-74-9891',20005589),
('Denver','97-96-8778',22162532),
('Denver','566-29-4340',3725886),
('Denver','833-83-6657',2989029),
('Denver','379-07-0890',11751295),
('Denver','587-92-6981',22926215),
('Denver','318-44-3806',1082576),
('Miami','238-76-2409',13798392),
('Miami','517-80-2900',16149222),
('Miami','43-75-2525',19203423),
('Miami','991-41-4717',9052763),
('Miami','586-02-6345',14361196),
('Miami','761-20-0113',20731386),
('Miami','304-76-4455',19139487),
('Miami','915-19-7187',24844199),
('Miami','522-42-5446',8151020),
('Miami','250-27-5139',15362537),
('Phoenix','222-83-2374',12181804),
('Phoenix','676-49-9001',2741998),
('Phoenix','954-79-5354',10554398),
('Phoenix','322-13-4545',19553764),
('Phoenix','123-39-9882',17825781),
('Phoenix','149-76-2174',13317879),
('Phoenix','458-75-7082',3823142),
('Phoenix','959-90-0495',16192195),
('Phoenix','576-03-4113',19624437),
('Phoenix','818-14-3462',15727346),
('San Franciso','601-08-0834',19826858),
('San Franciso','859-87-8937',15307583),
('San Franciso','973-23-5369',17121941),
('San Franciso','208-40-5800',3709959),
('San Franciso','766-39-4184',23956661),
('San Franciso','698-12-9881',7912142),
('San Franciso','370-36-5023',10771913),
('San Franciso','697-64-7726',17986615),
('San Franciso','469-80-7272',1058416),
('San Franciso','80-30-7883',14406017),
('Atlanta','794-03-1100',21372650),
('Atlanta','482-63-6639',12672468),
('Atlanta','293-18-9038',14326126),
('Atlanta','463-99-0774',4087683),
('Atlanta','884-99-4306',13166906),
('Atlanta','66-29-2542',23222697),
('Atlanta','578-08-8975',20989119),
('Atlanta','773-70-6586',14524444),
('Atlanta','607-76-2151',24381319),
('Atlanta','524-78-7296',21569517)
) n(city, id, salary)
 

This produces the following result.
                MEDIAN
----------------------
              14343661
 
(1 row(s) affected)
 

We can also take advantage of the grouping capabilities of the aggregate functions and calculate the median salary for each city.
SELECT city, wct.MEDIAN(salary) as MEDIAN
FROM (VALUES
('New York','781-40-1515',14901347),
('New York','282-59-4245',7891086),
('New York','471-76-6363',24082868),
('New York','817-54-9141',6343371),
('New York','604-44-4703',4736104),
('New York','195-53-8949',21626261),
('New York','326-09-8461',19252906),
('New York','592-00-9748',15614953),
('New York','498-95-6935',2263965),
('New York','219-24-4076',7969176),
('Los Angeles','157-79-4345',2545791),
('Los Angeles','840-20-4955',3844384),
('Los Angeles','25-32-7992',7501493),
('Los Angeles','536-60-2483',14150187),
('Los Angeles','730-94-1257',21130745),
('Los Angeles','971-32-9439',3479240),
('Los Angeles','693-55-3375',2428135),
('Los Angeles','44-00-1792',12562931),
('Los Angeles','260-97-4805',6650349),
('Los Angeles','885-70-9272',9926430),
('Chicago','164-72-0542',16107100),
('Chicago','687-97-8248',3794191),
('Chicago','754-78-1002',20529865),
('Chicago','163-56-7721',15674667),
('Chicago','765-24-0541',18766485),
('Chicago','769-33-7458',20106371),
('Chicago','181-31-5887',22686260),
('Chicago','386-42-7266',24437495),
('Chicago','537-03-6551',5513915),
('Chicago','454-31-9749',14760208),
('Dallas','10-15-4091',24277270),
('Dallas','818-17-1808',17138895),
('Dallas','168-96-8253',5038217),
('Dallas','978-11-2294',17251430),
('Dallas','153-86-6611',2865907),
('Dallas','325-06-9889',20413445),
('Dallas','306-24-6442',10423512),
('Dallas','242-34-5411',1318956),
('Dallas','175-98-7791',22356674),
('Dallas','889-65-2818',23469564),
('Boston','684-51-3410',8343599),
('Boston','567-22-2081',22520551),
('Boston','762-35-5534',8020738),
('Boston','673-92-6296',8499708),
('Boston','614-75-9862',14224087),
('Boston','974-01-1606',22539319),
('Boston','818-76-2325',9743214),
('Boston','119-36-0656',14199650),
('Boston','672-56-6167',12755767),
('Boston','485-77-9767',10015356),
('Denver','661-09-5879',5292759),
('Denver','256-03-6426',2031359),
('Denver','328-43-7297',18055202),
('Denver','405-74-9891',20005589),
('Denver','97-96-8778',22162532),
('Denver','566-29-4340',3725886),
('Denver','833-83-6657',2989029),
('Denver','379-07-0890',11751295),
('Denver','587-92-6981',22926215),
('Denver','318-44-3806',1082576),
('Miami','238-76-2409',13798392),
('Miami','517-80-2900',16149222),
('Miami','43-75-2525',19203423),
('Miami','991-41-4717',9052763),
('Miami','586-02-6345',14361196),
('Miami','761-20-0113',20731386),
('Miami','304-76-4455',19139487),
('Miami','915-19-7187',24844199),
('Miami','522-42-5446',8151020),
('Miami','250-27-5139',15362537),
('Phoenix','222-83-2374',12181804),
('Phoenix','676-49-9001',2741998),
('Phoenix','954-79-5354',10554398),
('Phoenix','322-13-4545',19553764),
('Phoenix','123-39-9882',17825781),
('Phoenix','149-76-2174',13317879),
('Phoenix','458-75-7082',3823142),
('Phoenix','959-90-0495',16192195),
('Phoenix','576-03-4113',19624437),
('Phoenix','818-14-3462',15727346),
('San Franciso','601-08-0834',19826858),
('San Franciso','859-87-8937',15307583),
('San Franciso','973-23-5369',17121941),
('San Franciso','208-40-5800',3709959),
('San Franciso','766-39-4184',23956661),
('San Franciso','698-12-9881',7912142),
('San Franciso','370-36-5023',10771913),
('San Franciso','697-64-7726',17986615),
('San Franciso','469-80-7272',1058416),
('San Franciso','80-30-7883',14406017),
('Atlanta','794-03-1100',21372650),
('Atlanta','482-63-6639',12672468),
('Atlanta','293-18-9038',14326126),
('Atlanta','463-99-0774',4087683),
('Atlanta','884-99-4306',13166906),
('Atlanta','66-29-2542',23222697),
('Atlanta','578-08-8975',20989119),
('Atlanta','773-70-6586',14524444),
('Atlanta','607-76-2151',24381319),
('Atlanta','524-78-7296',21569517)
) n(city, id, salary)
GROUP BY city

This produces the following result.
city                         MEDIAN
------------ ----------------------
Atlanta                  17756781.5
Boston                   11385561.5
Chicago                  17436792.5
Dallas                   17195162.5
Denver                      8522027
Los Angeles                 7075921
Miami                    15755879.5
New York                 11435261.5
Phoenix                  14522612.5
San Franciso               14856800
 
(10 row(s) affected)


We can use the aggregate function in the WHERE clause to only include the results for thoses cities having a medain less than 10,000,000.
SELECT city, wct.MEDIAN(salary) as MEDIAN
FROM (VALUES
('New York','781-40-1515',14901347),
('New York','282-59-4245',7891086),
('New York','471-76-6363',24082868),
('New York','817-54-9141',6343371),
('New York','604-44-4703',4736104),
('New York','195-53-8949',21626261),
('New York','326-09-8461',19252906),
('New York','592-00-9748',15614953),
('New York','498-95-6935',2263965),
('New York','219-24-4076',7969176),
('Los Angeles','157-79-4345',2545791),
('Los Angeles','840-20-4955',3844384),
('Los Angeles','25-32-7992',7501493),
('Los Angeles','536-60-2483',14150187),
('Los Angeles','730-94-1257',21130745),
('Los Angeles','971-32-9439',3479240),
('Los Angeles','693-55-3375',2428135),
('Los Angeles','44-00-1792',12562931),
('Los Angeles','260-97-4805',6650349),
('Los Angeles','885-70-9272',9926430),
('Chicago','164-72-0542',16107100),
('Chicago','687-97-8248',3794191),
('Chicago','754-78-1002',20529865),
('Chicago','163-56-7721',15674667),
('Chicago','765-24-0541',18766485),
('Chicago','769-33-7458',20106371),
('Chicago','181-31-5887',22686260),
('Chicago','386-42-7266',24437495),
('Chicago','537-03-6551',5513915),
('Chicago','454-31-9749',14760208),
('Dallas','10-15-4091',24277270),
('Dallas','818-17-1808',17138895),
('Dallas','168-96-8253',5038217),
('Dallas','978-11-2294',17251430),
('Dallas','153-86-6611',2865907),
('Dallas','325-06-9889',20413445),
('Dallas','306-24-6442',10423512),
('Dallas','242-34-5411',1318956),
('Dallas','175-98-7791',22356674),
('Dallas','889-65-2818',23469564),
('Boston','684-51-3410',8343599),
('Boston','567-22-2081',22520551),
('Boston','762-35-5534',8020738),
('Boston','673-92-6296',8499708),
('Boston','614-75-9862',14224087),
('Boston','974-01-1606',22539319),
('Boston','818-76-2325',9743214),
('Boston','119-36-0656',14199650),
('Boston','672-56-6167',12755767),
('Boston','485-77-9767',10015356),
('Denver','661-09-5879',5292759),
('Denver','256-03-6426',2031359),
('Denver','328-43-7297',18055202),
('Denver','405-74-9891',20005589),
('Denver','97-96-8778',22162532),
('Denver','566-29-4340',3725886),
('Denver','833-83-6657',2989029),
('Denver','379-07-0890',11751295),
('Denver','587-92-6981',22926215),
('Denver','318-44-3806',1082576),
('Miami','238-76-2409',13798392),
('Miami','517-80-2900',16149222),
('Miami','43-75-2525',19203423),
('Miami','991-41-4717',9052763),
('Miami','586-02-6345',14361196),
('Miami','761-20-0113',20731386),
('Miami','304-76-4455',19139487),
('Miami','915-19-7187',24844199),
('Miami','522-42-5446',8151020),
('Miami','250-27-5139',15362537),
('Phoenix','222-83-2374',12181804),
('Phoenix','676-49-9001',2741998),
('Phoenix','954-79-5354',10554398),
('Phoenix','322-13-4545',19553764),
('Phoenix','123-39-9882',17825781),
('Phoenix','149-76-2174',13317879),
('Phoenix','458-75-7082',3823142),
('Phoenix','959-90-0495',16192195),
('Phoenix','576-03-4113',19624437),
('Phoenix','818-14-3462',15727346),
('San Franciso','601-08-0834',19826858),
('San Franciso','859-87-8937',15307583),
('San Franciso','973-23-5369',17121941),
('San Franciso','208-40-5800',3709959),
('San Franciso','766-39-4184',23956661),
('San Franciso','698-12-9881',7912142),
('San Franciso','370-36-5023',10771913),
('San Franciso','697-64-7726',17986615),
('San Franciso','469-80-7272',1058416),
('San Franciso','80-30-7883',14406017),
('Atlanta','794-03-1100',21372650),
('Atlanta','482-63-6639',12672468),
('Atlanta','293-18-9038',14326126),
('Atlanta','463-99-0774',4087683),
('Atlanta','884-99-4306',13166906),
('Atlanta','66-29-2542',23222697),
('Atlanta','578-08-8975',20989119),
('Atlanta','773-70-6586',14524444),
('Atlanta','607-76-2151',24381319),
('Atlanta','524-78-7296',21569517)
) n(city, id, salary)
GROUP BY city
HAVING wct.MEDIAN(salary) < 10000000

This prodcues the following result.
city                         MEDIAN
------------ ----------------------
Denver                      8522027
Los Angeles                 7075921
 

(2 row(s) affected)



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service