MySQL Median: Statistical Aggregate Functions

Besides totals, MySQL can calculate basic statistics.

query.sql

SQL

CREATE TABLE response_times (id INT AUTO_INCREMENT PRIMARY KEY, ms INT NOT NULL);
INSERT INTO response_times (ms) VALUES (120), (135), (150), (110), (980), (140), (125), (130);

SELECT COUNT(*) AS samples,
       ROUND(AVG(ms), 1)          AS mean,
       ROUND(STDDEV_POP(ms), 1)   AS stddev_population,
       ROUND(STDDEV_SAMP(ms), 1)  AS stddev_sample,
       ROUND(VAR_POP(ms), 0)      AS variance_population,
       MIN(ms) AS fastest,
       MAX(ms) AS slowest
FROM response_times;

Output

OUTPUT

+---------+-------+-------------------+---------------+---------------------+---------+---------+
| samples | mean  | stddev_population | stddev_sample | variance_population | fastest | slowest |
+---------+-------+-------------------+---------------+---------------------+---------+---------+
|       8 | 236.3 |             281.3 |         300.8 |               79155 |     110 |     980 |
+---------+-------+-------------------+---------------+---------------------+---------+---------+
1 row in set

Function	Meaning
`STDDEV_POP(x)` (also `STDDEV`, `STD`)	Standard deviation of the whole population
`STDDEV_SAMP(x)`	Standard deviation of a sample (divides by n – 1)
`VAR_POP(x)` (also `VARIANCE`)	Variance of the population
`VAR_SAMP(x)`	Variance of a sample

Use the _SAMP versions when your rows are a sample of a bigger set, and _POP when you have all of it. The word STDDEV alone means the population version in MySQL (it may mean the sample version in other databases).

The slow response (980 ms) pulls the mean up and makes the deviation large. This is why the median is often a better “typical value”.

The median: no built-in function

MySQL has no MEDIAN. With window functions, it is a short query. The median is the middle value, or the average of the two middle values when the count is even:

query.sql

SQL

SELECT AVG(ms) AS median
FROM (
    SELECT ms,
           ROW_NUMBER() OVER (ORDER BY ms) AS position,
           COUNT(*)     OVER ()            AS total
    FROM response_times
) AS ordered
WHERE position IN (FLOOR((total + 1) / 2), CEIL((total + 1) / 2));

Output

OUTPUT

+----------+
| median   |
+----------+
| 132.5000 |
+----------+
1 row in set

With 8 values, the median is the average of the 4th and 5th: (130 + 135) / 2 = 132.5. Compare with the mean of 236.25, which the single slow request has distorted.

Percentiles

A percentile is the value below which a given percent of the rows fall. The 90th percentile (“p90”) of response times is a common service-level measure. Use NTILE or a position calculation:

query.sql

SQL

SELECT ms AS p75_approximately
FROM (
    SELECT ms, ROW_NUMBER() OVER (ORDER BY ms) AS position, COUNT(*) OVER () AS total
    FROM response_times
) AS ordered
WHERE position = CEIL(0.75 * total);

Output

OUTPUT

+-------------------+
| p75_approximately |
+-------------------+
|               140 |
+-------------------+
1 row in set

The 75th percentile uses the row at position CEIL(0.75 * 8) = 6, which is 140 ms. There are several definitions of a percentile (interpolation methods), so numbers may differ slightly from other tools.

Buckets with NTILE

query.sql

SQL

SELECT ms, NTILE(4) OVER (ORDER BY ms) AS quartile FROM response_times ORDER BY ms;

Output

OUTPUT

+-----+----------+
| ms  | quartile |
+-----+----------+
| 110 |        1 |
| 120 |        1 |
| 125 |        2 |
| 130 |        2 |
| 135 |        3 |
| 140 |        3 |
| 150 |        4 |
| 980 |        4 |
+-----+----------+
8 rows in set

NTILE(4) splits the ordered rows into four equal groups: quartiles.

Other aggregates you may meet

Function	Use
`BIT_AND`, `BIT_OR`, `BIT_XOR`	Bitwise combine of integer values (flags)
`JSON_ARRAYAGG`, `JSON_OBJECTAGG`	Build JSON (GROUP_CONCAT)
`ANY_VALUE(x)`	Tells MySQL “I do not care which value”, to satisfy `ONLY_FULL_GROUP_BY`

query.sql

SQL

SELECT BIT_OR(flag) AS combined_flags FROM (SELECT 1 AS flag UNION ALL SELECT 4 UNION ALL SELECT 2) AS t;

Output

OUTPUT

+----------------+
| combined_flags |
+----------------+
|              7 |
+----------------+
1 row in set

lightbulb

Outliers

A mean without a median or a maximum can mislead. Report both when the data can have extreme values (response times, order values, salaries).