Lesson 21 of 61

5 mins read

SQL Aggregate Functions: Statistical Summaries

In Plain English

SQL Aggregate functions perform calculations on multiple rows of a column and collapse them into a single summary metric. The five standard ANSI aggregate functions are `COUNT()` (number of rows), `SUM()` (total sum), `AVG()` (arithmetic mean), `MIN()` (lowest value), and `MAX()` (highest value).

The Mental Model

"An aggregate function is like a financial accountant: rather than reading 10,000 individual expense receipts aloud, they summarize: "We had 10,000 receipts totaling $450,000, averaging $45 each.""

Deep Dive: How It Works

Null Handling: All aggregate functions (except `COUNT(*)`) ignore NULL values during computation.

Scalar Aggregation: Running aggregate functions without `GROUP BY` collapses the entire table into exactly one single result row.

Combining Aggregates: Multiple aggregate functions can be executed simultaneously in a single query (`SELECT COUNT(*), AVG(salary), MAX(salary) FROM employees`).

Syntax Blueprint

SELECT
  COUNT(*) AS total_orders,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_value,
  MIN(amount) AS min_sale,
  MAX(amount) AS max_sale
FROM sales;

Aggregate functions summarize thousands of individual rows into concise high-level metrics.

Core Rules to Remember

5 Core Functions: COUNT, SUM, AVG, MIN, and MAX form the foundation of SQL data analytics.

Single Row Output: Without GROUP BY, aggregate queries always return a single summary row.

Null Exclusion: AVG(score) calculates total / non-null rows, avoiding distortion from missing values.

Common Beginner Traps & How to Fix Them

Mistake:

Mixing an unaggregated column with aggregate functions without a GROUP BY clause (`SELECT department, AVG(salary) FROM employees`).

Why it happens: In standard SQL, projecting an unaggregated column alongside aggregates is invalid because the engine cannot determine which department to pair with the single overall average.

How to fix: Add `GROUP BY department` or use window functions.

Pro Tip: Use `ROUND(AVG(col), 2)` to avoid long floating point precision strings in reporting dashboards.

Live Interactive Example

Hit Run Code to see it live

Computing Key Metrics with Aggregate Functions

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: The 5 aggregate functions calculated count (4), sum (180 units), average ($100), min ($25), and max ($250).

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Compute Aggregate Summary

Select `COUNT(*) AS count`, `SUM(score) AS total`, `AVG(score) AS avg` from `grades`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.