Descriptive Statistics with SQL | Learn SQL

This page walks through a complete descriptive-statistics workflow using nothing but SQL — the exact queries you would run directly against a research database before ever exporting data to R or Python.

Why Compute Statistics Directly in SQL

For large datasets, computing summary statistics inside the database - before exporting anything - is often far faster than pulling millions of raw rows into R or Python first. This page uses a simulated research_data table with the same shape used on the R and Python versions of this guide, so the results below are directly comparable.

R Code

-- research_data: student_id | gender | class_group | study_hours | exam_score
-- 60 rows total, roughly evenly split by gender and class_group

CREATE TABLE research_data (
  student_id   INTEGER PRIMARY KEY,
  gender       TEXT,
  class_group  TEXT,   -- "Control" or "Treatment"
  study_hours  REAL,
  exam_score   REAL
);

Step 1: First Look at the Data

R Code

SELECT COUNT(*) AS total_rows FROM research_data;

SELECT * FROM research_data LIMIT 5;

Output

total_rows
60

student_id | gender | class_group | study_hours | exam_score
1          | Male   | Control     | 6.3         | 68.2
2          | Female | Treatment   | 4.8         | 71.5
3          | Male   | Control     | 5.5         | 63.9
4          | Female | Control     | 3.9         | 74.1
5          | Male   | Treatment   | 6.1         | 82.0

Step 2: Central Tendency & Dispersion

Standard SQL has no built-in MEDIAN() or STDEV() in every dialect - PostgreSQL supports both natively; other databases need small workarounds.

R Code

SELECT
  ROUND(AVG(exam_score), 2)              AS mean_score,
  ROUND(STDDEV(exam_score), 2)           AS sd_score,   -- PostgreSQL / SQL Server
  MIN(exam_score)                          AS min_score,
  MAX(exam_score)                          AS max_score,
  MAX(exam_score) - MIN(exam_score)         AS range_score
FROM research_data;

Output

mean_score | sd_score | min_score | max_score | range_score
70.12      | 9.85     | 49.3      | 92.4      | 43.1

💡 Tip

SQLite has no STDDEV() built in - compute variance manually with AVG((x - mean)^2), or use a small extension. PostgreSQL, MySQL 8+, and SQL Server all support STDDEV() (or STDEV() in SQL Server) directly.

Step 3: Median with PERCENTILE_CONT

R Code

-- PostgreSQL / SQL Server style
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY exam_score) AS median_score
FROM research_data;

Output

median_score
69.85

Step 4: Frequency Distribution

R Code

SELECT class_group, COUNT(*) AS n,
  ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM research_data), 1) AS pct
FROM research_data
GROUP BY class_group;

Output

class_group | n  | pct
Control     | 31 | 51.7
Treatment   | 29 | 48.3

Step 5: Cross-Tabulation with CASE + GROUP BY

R Code

SELECT gender,
  SUM(CASE WHEN class_group = "Control"   THEN 1 ELSE 0 END) AS control,
  SUM(CASE WHEN class_group = "Treatment" THEN 1 ELSE 0 END) AS treatment
FROM research_data
GROUP BY gender;

Output

gender | control | treatment
Female | 15      | 14
Male   | 16      | 15

Step 6: Grouped Descriptive Statistics

R Code

SELECT class_group,
  COUNT(*)                       AS n,
  ROUND(AVG(exam_score), 2)    AS mean_score,
  ROUND(STDDEV(exam_score), 2) AS sd_score
FROM research_data
GROUP BY class_group;

Output

class_group | n  | mean_score | sd_score
Control     | 31 | 68.41      | 9.63
Treatment   | 29 | 71.95      | 10.02

Step 7: Correlation

PostgreSQL and MySQL both provide a built-in CORR() aggregate function for the Pearson correlation coefficient.

R Code

SELECT ROUND(CORR(study_hours, exam_score), 3) AS correlation
FROM research_data;

Output

correlation
0.483

Advanced: Manual Standard Deviation (for Databases Without STDDEV)

🚀 Advanced Example

SQLite has no STDDEV() function, but standard deviation can always be computed manually from its definition using a CTE for the mean.

R Code

WITH stats AS (
  SELECT AVG(exam_score) AS mean_score FROM research_data
)
SELECT
  ROUND(
    SQRT(SUM((r.exam_score - s.mean_score) * (r.exam_score - s.mean_score)) / (COUNT(*) - 1)),
    2
  ) AS sd_score
FROM research_data r, stats s;

Output

sd_score
9.85

Reporting Results in a Research Write-Up

Every summary number above traces to one specific query - exactly the kind of traceability a reviewer or collaborator would want to verify: "The sample consisted of 60 students (31 Control, 29 Treatment). Mean exam score was 70.12 (SD = 9.85). Students in the Treatment group scored higher on average (M = 71.95, SD = 10.02) than the Control group (M = 68.41, SD = 9.63), and study hours correlated moderately with exam score, r = .48."

Key Points

AVG, MIN, MAX, and STDDEV compute core descriptive statistics directly inside the database.
PERCENTILE_CONT(0.5) computes the median in PostgreSQL/SQL Server; SQLite needs a manual workaround.
CASE + GROUP BY is the standard SQL pattern for frequency tables and cross-tabulations.
CORR(x, y) computes the Pearson correlation coefficient directly, where supported.
When a function like STDDEV is unavailable, it can always be computed manually from its mathematical definition using a CTE.

← Previous

Window Functions