Descriptive Statistics in SQL: Beyond Average and Count

Most SQL beginners know how to calculate a total with SUM or an average with AVG. But if you're working in data analysis, finance, or data science, basic averages are rarely enough. Averages can be heavily distorted by outliers, and they don't tell you anything about the distribution of your data.

To truly understand your data, you need Descriptive Statistics. In this guide, we'll go beyond the basics and learn how to calculate median, mode, percentiles, and variance using standard SQL.

A Real Business Scenario: Pricing and Salary Benchmarks That Ignore Outliers

Descriptive statistics become useful the moment a team needs to summarize a messy distribution without lying to itself.

Imagine you are analyzing:

• marketplace order values with a few extremely large enterprise deals
• employee salaries where executive compensation dwarfs the rest of the company
• page-load times where a small set of slow requests drags up the average

If you report only the mean, the summary can be technically correct and operationally misleading. Median, percentiles, and spread metrics exist because "average" often hides the shape of the data that decisions actually depend on.

1. The Median: The True Middle

The average (mean) is sensitive to extreme values. The Median is the middle value when data is sorted. It's much more robust for things like housing prices or employee salaries.

While some databases have a built-in MEDIAN() function, others require a bit more work.

-- Using percentile_cont (PostgreSQL, SQL Server, etc.)
SELECT 
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

In SQLite (which we use for our playground), we can calculate it by sorting and picking the middle row:

Insight: Notice how the average salary is $239,000, but the median is $50,000. The median gives a much better sense of what a "typical" employee earns.

A Common Mistake: Treating One Summary Metric as the Whole Story

Analysts often compute one descriptive statistic and stop there.

That causes different kinds of mistakes:

• using the mean on a highly skewed dataset
• using the median when the tails matter operationally
• reporting a percentile without clarifying the population, time window, or grain
• comparing spread metrics across groups with radically different sample sizes

The safe pattern is to use several complementary summaries together. Mean tells you central tendency. Median tells you robustness against outliers. Percentiles show tails. Standard deviation shows spread. Each metric answers a different question.

2. The Mode: The Most Frequent Value

The Mode is the value that appears most often in your dataset. This is useful for answering questions like "What is our best-selling product category?" or "Where do most of our users live?"

SELECT category, COUNT(*) as frequency
FROM sales
GROUP BY category
ORDER BY frequency DESC
LIMIT 1;

3. Percentiles: Understanding Distribution

Percentiles (like the 90th or 95th percentile) are essential for performance monitoring (P95 latency) and grading. They tell you the value below which a given percentage of data falls.

4. Range and Spread

The simplest measure of spread is the Range—the difference between the maximum and minimum values. While simple, it helps you identify the boundaries of your dataset.

SELECT 
  MIN(price) as min_price,
  MAX(price) as max_price,
  MAX(price) - MIN(price) as price_range
FROM products;

5. Standard Deviation and Variance (The Advanced Stuff)

Standard Deviation measures how spread out your numbers are from the average. A low standard deviation means the data is clustered closely around the mean, while a high one means it's widely dispersed.

Most enterprise databases (Postgres, Oracle, SQL Server) provide:

• STDDEV() or STDEV()
• VARIANCE() or VAR()

Boundary and Performance Notes

Descriptive statistics look lightweight, but the details matter:

• percentiles and medians usually require sorting, which can become expensive on large partitions
• approximate percentile functions may be faster, but they trade exactness for speed
• null handling changes results, especially when sparse metrics are mixed with real zeroes
• the grain of the input table matters more than the function name, because row-level events and already-aggregated rows answer different questions

Before optimizing the SQL, lock the analytical grain. A percentile on session rows means something very different from a percentile on user-level averages.

When NOT to Stop at Descriptive Statistics

Descriptive statistics are useful summaries, not explanations. They are not enough when:

• you need to estimate causal impact rather than describe a distribution
• the business question depends on segments hidden inside the aggregate
• the time trend matters more than the overall distribution
• the sample is too small for stable interpretation

In those cases, descriptive statistics should be the starting point, not the final analysis.

Official References

• PostgreSQL ordered-set aggregate documentation for percentile_cont and related percentile functions.
• SQLite window functions documentation for the ranking and row-order tools often used to emulate medians in SQLite.
• NIST Engineering Statistics Handbook for the statistical meaning behind central tendency and spread measures.

Summary Table

Statistic	What it tells you	SQL Keyword/Pattern
Mean	The arithmetic average	AVG()
Median	The "middle" value	PERCENTILE_CONT(0.5)
Mode	The most common value	GROUP BY + ORDER BY + LIMIT 1
Range	The spread (Max - Min)	MAX() - MIN()
Std Dev	How dispersed data is	STDDEV()

Conclusion

Mastering descriptive statistics in SQL helps you summarize messy real-world data without flattening away the interesting parts. The key is not memorizing one formula. It is choosing the statistic that matches the business question and the shape of the data.

Next time you're asked for a "summary report," don't just provide the average. Include the median and the range to tell the full story of your data.

Try it out!

Use the playground snippet above to add more "outlier" salaries and see how the average moves while the median stays stable!

Related Articles

• Calculating Percentiles and Median in SQL for a deeper look at robust statistics beyond the mean.
• SQL Histograms and Frequency Distributions for the shape-of-distribution view that pairs well with summary statistics.
• Analyzing A/B Test Results with SQL for a practical use case where statistical summaries feed real product decisions.