MEDIAN
Updated 2023-10-23 20:31:18.323000
Syntax
SELECT [westclintech].[wct].[MEDIAN] (
<@x, float,>)
Description
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.
Arguments
@x
the known x-values to be used in the MEDIAN calculation. @x is an expression of type float or of a type that can be implicitly converted to float.
Return Type
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.
Examples
SELECT wct.MEDIAN(x) as MEDIAN
FROM
(
VALUES
(1),
(2),
(4),
(7),
(8),
(9),
(10),
(12)
) n (x);
This produces the following result
{"columns":[{"field":"MEDIAN","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"MEDIAN":"7.5"}]}
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.
{"columns":[{"field":"MEDIAN","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"MEDIAN":"14343661"}]}
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.
{"columns":[{"field":"city"},{"field":"MEDIAN","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"city":"Atlanta","MEDIAN":"17756781.5"},{"city":"Boston","MEDIAN":"11385561.5"},{"city":"Chicago","MEDIAN":"17436792.5"},{"city":"Dallas","MEDIAN":"17195162.5"},{"city":"Denver","MEDIAN":"8522027"},{"city":"Los Angeles","MEDIAN":"7075921"},{"city":"Miami","MEDIAN":"15755879.5"},{"city":"New York","MEDIAN":"11435261.5"},{"city":"Phoenix","MEDIAN":"14522612.5"},{"city":"San Franciso","MEDIAN":"14856800"}]}
We can use the aggregate function in the WHERE clause to only include the results for those cities having a median 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 produces the following result.
{"columns":[{"field":"city"},{"field":"MEDIAN","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"city":"Denver","MEDIAN":"8522027"},{"city":"Los Angeles","MEDIAN":"7075921"}]}