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