SQL Median

Tools & Utilities
Enthusiast

SQL Median

Does anyone know how to calculate the median using SQL?
2 REPLIES
Junior Contributor

Re: SQL Median

DROP table median_test;

CREATE MULTISET TABLE median_test
(
id INTEGER
,med_group INTEGER
,med_value INTEGER
) PRIMARY INDEX (id);

INSERT INTO median_test VALUES (1, 1, 1);
INSERT INTO median_test VALUES (2, 1, 2);
INSERT INTO median_test VALUES (3, 1, 3);
INSERT INTO median_test VALUES (4, 1, 5);

INSERT INTO median_test VALUES (5, 2, 1);
INSERT INTO median_test VALUES (6, 2, 6);
INSERT INTO median_test VALUES (7, 2, 7);

INSERT INTO median_test VALUES (8, 3, 1);

INSERT INTO median_test VALUES (9, 4, 4);
INSERT INTO median_test VALUES (10, 4, 5);
INSERT INTO median_test VALUES (11, 4, 6);
INSERT INTO median_test VALUES (12, 4, 6);
INSERT INTO median_test VALUES (13, 4, 7);
INSERT INTO median_test VALUES (14, 4, 7);
INSERT INTO median_test VALUES (15, 4, 7);
INSERT INTO median_test VALUES (16, 4, 8);

/*** "financial median": Group COUNT + ROW_NUMBER ***/
SELECT
dt1.med_group
,AVG(med_value)
FROM
(SELECT
med_group
,med_value
,ROW_NUMBER() OVER (PARTITION BY med_group
ORDER BY med_value) AS row_num
,COUNT(*) OVER (PARTITION BY med_group
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS row_count
FROM median_test
) AS dt1
WHERE
row_num = (row_count + 1) / 2
OR
row_num = (row_count / 2) + 1
GROUP BY med_group
ORDER BY med_group
;

/*** "statistical median": Group COUNT + ROW_NUMBER ***/
SELECT
dt1.med_group
,med_value
FROM
(SELECT
med_group
,med_value
,ROW_NUMBER() OVER (PARTITION BY med_group
ORDER BY med_value) AS row_num
,COUNT(*) OVER (PARTITION BY med_group
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS row_count
FROM median_test
) AS dt1
WHERE
row_num = (row_count + 1) / 2 --left (lesser) value
-- row_num = (row_count / 2) + 1 --right (greater) value
ORDER BY med_group
;

Dieter

Re: SQL Median

If you google for "Joe Celko Median Workbench", then you will find several different examples of median equivalent SQL.