Using Statistical Functions in SQL: Median, Percentiles, Spread

1. Why AVG alone is not enough

The average, computed with AVG, is the most commonly used metric in SQL reports, but it distorts the picture as soon as outliers are present in the data. A single large order can skew the average order value of an entire region upward, even though the typical order is significantly smaller. This is exactly where statistical functions in SQL come in: they deliver more robust and more meaningful metrics than the plain mean.

The median, for instance, is considerably more robust against outliers than AVG, because it describes the middle value of a sorted distribution instead of summing all values with equal weight. Statistical functions in SQL such as PERCENTILE_CONT, STDDEV, and VARIANCE make it possible to compute such more robust metrics directly in the database, without exporting the raw data to an external statistics tool.

The practical advantage of computing statistical functions in SQL directly in the database rather than in the application layer lies in efficiency: the database processes the raw data anyway, and an additional aggregate computation costs considerably less than transporting all individual values across the network into a separate analysis tool.

2. Computing the median with PERCENTILE_CONT and PERCENTILE_DISC

The median is the value that splits a sorted distribution exactly in half, so that half the values fall below it and half above. In PostgreSQL, Oracle, and SQL Server, the median is computed with the function PERCENTILE_CONT(0.5) inside a WITHIN GROUP clause. PERCENTILE_CONT interpolates between two adjacent values when the exact middle falls between two data points, while PERCENTILE_DISC always returns a value actually present in the distribution.

The difference between PERCENTILE_CONT and PERCENTILE_DISC matters for even sized distributions: with an even number of values, the mathematical median lies exactly between the two middle values, which PERCENTILE_CONT correctly represents through interpolation, while PERCENTILE_DISC instead picks one of the two adjacent original values. For genuine statistical accuracy, PERCENTILE_CONT is the right choice, for selecting an actually existing record, PERCENTILE_DISC.

-- PostgreSQL / Oracle: median with interpolation
SELECT
    product_category,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_value) AS median_order_value,
    AVG(order_value) AS avg_order_value
FROM orders
GROUP BY product_category;

-- MySQL 8 has no native PERCENTILE_CONT — simulate with window functions
SELECT DISTINCT
    product_category,
    PERCENT_RANK() OVER (PARTITION BY product_category ORDER BY order_value) AS rank
FROM orders;

MySQL, up to version 8.0, has no native PERCENTILE_CONT function and requires a manual solution via window functions like ROW_NUMBER and COUNT to identify the middle row of a sorted group. This manual median calculation is more cumbersome but functionally equivalent to PERCENTILE_CONT in the other databases.

3. Percentiles beyond the median

PERCENTILE_CONT accepts any value between 0 and 1, not just 0.5 for the median. A value of 0.9 delivers the 90th percentile, that is, the value below which 90 percent of all observations fall. This metric is particularly relevant for performance metrics such as load times, where the p95 or p99 percentile is more meaningful than the average, because it shows how bad the experience actually is for the slowest users.

In practice, several percentiles are often computed together in a single query to get a complete picture of the distribution: the 25th, 50th, 75th, and 95th percentile together show both the central tendency and the spread at the edges of the distribution. This combination of several statistical functions in SQL often replaces an entire boxplot, without needing a separate visualization tool.

-- Multiple percentiles in a single query
SELECT
    product_category,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY response_time_ms) AS p25,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY response_time_ms) AS p50,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY response_time_ms) AS p75,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time_ms) AS p95
FROM request_log
WHERE request_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY product_category;

4. Measuring spread: STDDEV and VARIANCE

While median and percentiles describe the location of a distribution, STDDEV and VARIANCE measure how strongly the values scatter around the mean. Standard deviation, computed with STDDEV() or, alternatively in PostgreSQL, STDDEV_SAMP(), is the square root of the variance and is expressed in the same unit as the original data, which makes its interpretation easier. This statistical function in SQL is essential for judging whether an average value represents a homogeneous or a strongly fluctuating group.

An important distinction concerns the choice between sample and population variance. STDDEV_SAMP() and VAR_SAMP() divide by n minus 1 and are the right choice when the available data is a sample of a larger population. STDDEV_POP() and VAR_POP() divide by n and are correct when the data represents the complete population itself, for instance all orders in a closed time period.

-- Standard deviation and variance, sample vs population
SELECT
    region,
    AVG(order_value) AS average,
    STDDEV_SAMP(order_value) AS std_sample,
    STDDEV_POP(order_value) AS std_population,
    VAR_SAMP(order_value) AS variance_sample
FROM orders
GROUP BY region
HAVING COUNT(*) > 1;  -- STDDEV_SAMP requires at least 2 rows per group

A common mistake: STDDEV_SAMP() with only one row in the group returns NULL, since the formula divides by n minus 1, which equals zero. Anyone who does not exclude these cases with a HAVING condition gets NULL values in groups with only one observation, which can lead to unexpected results in downstream calculations.

5. Mode and distribution shape without a built-in function

Unlike the median and standard deviation, the mode, that is, the most frequent value of a distribution, has no built in aggregate function in most databases. The mode can nonetheless be simulated with a combination of GROUP BY, COUNT, and ORDER BY: you group by the value in question, count its frequency, sort descending by that frequency, and take the top row.

For the shape of a distribution, for instance skewness or kurtosis, only a few databases offer native functions. Oracle provides dedicated functions for this, PostgreSQL and MySQL require manually constructed formulas based on powers and AVG. For most reporting use cases, however, median, percentiles, and standard deviation are sufficient to characterize a distribution adequately, without needing to fall back on more complex metrics like skewness.

-- Mode: most frequent value, simulated without a built-in function
SELECT rounded_order_value, COUNT(*) AS frequency
FROM (
    SELECT ROUND(order_value, -1) AS rounded_order_value
    FROM orders
) AS rounded_values
GROUP BY rounded_order_value
ORDER BY frequency DESC
LIMIT 1;

6. Detecting relationships with CORR and regression

Besides the location and spread of individual columns, the relationship between two columns is often of interest. The function CORR(column_a, column_b) computes the Pearson correlation coefficient and returns a value between minus one and one. A value near one indicates a strong positive relationship, a value near minus one a strong negative relationship, and a value near zero no linear relationship.

PostgreSQL and Oracle additionally offer regression functions such as REGR_SLOPE() and REGR_INTERCEPT(), which compute a simple linear regression directly in SQL. These statistical functions in SQL are useful for running quick trend analyses, for instance whether there is a relationship between marketing spend and revenue, without exporting the data to a separate statistics tool.

-- Correlation and simple linear regression directly in SQL
SELECT
    CORR(marketing_spend, revenue) AS correlation,
    REGR_SLOPE(revenue, marketing_spend) AS slope,
    REGR_INTERCEPT(revenue, marketing_spend) AS intercept,
    REGR_R2(revenue, marketing_spend) AS r_squared
FROM monthly_figures;

7. Statistical functions as window functions

All statistical functions in SQL shown so far can also be used as window functions with an OVER clause, instead of collapsing rows into a single group. This allows displaying the median or standard deviation of a group next to every individual detail row, which is particularly useful for outlier detection: every row can be compared directly with the group wide average and standard deviation, without needing an additional self join.

This technique is often used for Z-score calculation, a standard measure for the distance of a value from the mean in units of standard deviation. A Z-score above 2 or below minus 2 typically marks a statistical outlier that deserves closer inspection.

-- Z-score per row, using window functions instead of a self-join
SELECT
    order_id,
    order_value,
    AVG(order_value) OVER (PARTITION BY region) AS avg_region,
    STDDEV_SAMP(order_value) OVER (PARTITION BY region) AS std_region,
    (order_value - AVG(order_value) OVER (PARTITION BY region))
        / NULLIF(STDDEV_SAMP(order_value) OVER (PARTITION BY region), 0) AS z_score
FROM orders;

8. Availability by database compared

Support for statistical functions in SQL differs significantly between the major database systems. PostgreSQL and Oracle offer the broadest native support, including percentiles, standard deviation, variance, correlation, and regression functions. SQL Server supports PERCENTILE_CONT, STDDEV, and VARIANCE, but no native REGR functions. MySQL only introduced STDDEV and VARIANCE with version 8.0, PERCENTILE_CONT is still completely missing to this day.

9. Practical example: detecting outliers in order data

A complete practical example combines several statistical functions in SQL to detect outliers in order data automatically. Instead of scrolling manually through thousands of orders, the query directly filters out all orders whose value deviates more than two standard deviations from the regional average. This technique is frequently used in fraud detection and quality control, because it automatically reacts to regional differences in order size, instead of using a single global threshold.

The combination of window functions for group related statistics and a final WHERE condition on the computed Z-score shows how statistical functions in SQL solve practical problems that would otherwise require a separate analysis script.

-- Detect statistical outliers using a two-standard-deviation threshold
WITH with_zscore AS (
    SELECT
        order_id,
        region,
        order_value,
        (order_value - AVG(order_value) OVER (PARTITION BY region))
            / NULLIF(STDDEV_SAMP(order_value) OVER (PARTITION BY region), 0) AS z_score
    FROM orders
)
SELECT order_id, region, order_value, ROUND(z_score, 2) AS z_score
FROM with_zscore
WHERE ABS(z_score) > 2
ORDER BY ABS(z_score) DESC;

10. Summary

Statistical functions in SQL make it possible to compute more robust metrics than the plain average directly in the database. PERCENTILE_CONT delivers the median and arbitrary percentiles, STDDEV and VARIANCE measure the spread around the mean, and CORR along with the REGR functions show linear relationships between two columns. Used as window functions, the same functions can display group wide metrics right next to every detail row, enabling outlier detection with Z-scores without a self join.

The availability of these functions differs greatly between databases: PostgreSQL and Oracle offer the most complete support, MySQL remains limited for percentiles and correlation. Consistently using statistical functions in SQL saves the detour via external analysis tools for many everyday reporting questions.

11. FAQ: Statistical functions in SQL