SQL Statistical Aggregate Functions

AVG, MIN, and MAX come with every database. The functions on this page describe the rest of a distribution. The names below are DuckDB's, which is what runs on this site; each section says what PostgreSQL and MySQL call the same thing.

select count(*)                    as products,
       round(avg(price), 2)        as avg_price,
       median(price)               as median_price,
       round(stddev_samp(price), 2) as price_stddev,
       min(price)                  as min_price,
       max(price)                  as max_price
from products

The functions

Like every aggregate except count(*), they skip NULLs, and they work with GROUP BY and FILTER as usual.

Median and percentiles

select   c.category_name,
         median(p.price)         as median_price,
         round(avg(p.price), 2)  as avg_price
from     products as p
join     categories as c on c.category_id = p.category_id
group by c.category_name
order by c.category_name

Tools has a median of 9.50 and an average of 13.17: one $22 tool lifts the average, not the median. Pass a list of fractions to get several percentiles in one call. The result is a list:

select quantile_cont(price, [0.25, 0.5, 0.75]) as quartiles
from products

PostgreSQL writes percentiles as ordered-set aggregates, and DuckDB accepts the same syntax: percentile_cont(0.5) within group (order by price), and percentile_disc for the discrete version. MySQL has no percentile aggregate. SQL Server's PERCENTILE_CONT is a window function and needs an OVER clause.

Spread

select round(stddev_samp(price), 2) as sample_stddev,
       round(stddev_pop(price), 2)  as population_stddev,
       round(var_samp(price), 2)    as sample_variance
from products

Most common value

select mode(status) as most_common_status
from orders

mode hides its count and settles ties silently. When either matters, count per value and rank the counts, as in the most common value lesson. PostgreSQL spells it mode() within group (order by status).

Correlation

select round(corr(price, cost), 3) as price_cost_corr
from products

0.992 is close to 1: price and cost rise together almost in a straight line. PostgreSQL has corr too, along with the regr_* family (regr_slope, regr_intercept), which DuckDB also runs.

Outliers

with scored as (
  select product_name,
         price,
         round((price - avg(price) over ()) / stddev_samp(price) over (), 2) as z_score
  from products
)
select product_name, price, z_score
from scored
where abs(z_score) > 1.5
order by z_score desc

A z-score is how many standard deviations a value sits from the average. The aggregates run as window functions here, with an empty over (), so every row can be compared with the whole table without a join.

Histograms

select   floor(price / 10) * 10 as price_band,
         count(*)               as products
from     products
group by price_band
order by price_band

Equal-width bands and a GROUP BY work in every database. DuckDB's histogram(floor(price / 10) * 10) returns the same counts as a single map. The histogram lesson covers named bands and keeping the empty ones.

Approximate counts

select approx_count_distinct(customer_id) as approx_customers,
       count(distinct customer_id)        as exact_customers
from orders