Aggregation Functions
Summarize data with COUNT, SUM, AVG, MIN, and MAX aggregate functions.
What you'll learn
- What aggregate functions are and when to use them
- COUNT, SUM, AVG, MIN, MAX — the five core aggregates
- The difference between COUNT(*) and COUNT(column)
- Combining aggregates with WHERE to filter before aggregating
- Why NULL values affect aggregate results
Concept
Aggregate Functions
An aggregate function takes many rows and returns a single value. They are the foundation of reporting and analytics — "how many orders?", "what is the average price?", "what is the total revenue?". The five core aggregates are:
| Function | Returns | Example | |----------|---------|---------| | COUNT(*) | Number of rows | COUNT(*) FROM orders | | SUM(col) | Total of numeric values | SUM(total) FROM orders | | AVG(col) | Average value | AVG(price) FROM products | | MIN(col) | Smallest value | MIN(price) FROM products | | MAX(col) | Largest value | MAX(created_at) FROM orders |
COUNT(*) vs COUNT(column)
COUNT(*) counts all rows, including those with NULL values. COUNT(column) counts only non-NULL values in that column. COUNT(DISTINCT column) counts unique non-NULL values. This distinction matters when your column has NULLs — COUNT(email) will skip rows where email is NULL.
Filtering with WHERE
You can combine aggregates with WHERE to filter rows before aggregating. SELECT AVG(price) FROM products WHERE category = 'electronics' gives the average price of electronics only. The WHERE clause is applied first, then the aggregate is computed over the remaining rows.
NULL Handling
All aggregate functions except COUNT(*) ignore NULL values. AVG(price) sums non-NULL prices and divides by the count of non-NULL prices — NULL rows do not pull the average toward zero. This is usually what you want, but be aware of it when your data has many NULLs.
Example
-- Core aggregates over the orders table
SELECT
COUNT(*) AS total_orders,
SUM(total) AS revenue,
AVG(total) AS avg_order_value,
MIN(total) AS smallest_order,
MAX(total) AS largest_order
FROM orders
WHERE status = 'completed';
-- Count distinct categories and total products
SELECT
COUNT(DISTINCT category) AS unique_categories,
COUNT(*) AS total_products
FROM products;
-- Revenue per category (preview of GROUP BY)
SELECT category, SUM(total) AS revenue
FROM orders
GROUP BY category;
The first query computes five aggregates in one pass. The second shows COUNT(DISTINCT). The third previews GROUP BY — coming next.
Try it
- SQL Formatter
Format queries with multiple aggregate functions.
- CSV to JSON
Export aggregated query results as JSON for charts.
Common mistakes
The mistake
Using COUNT(column) when you mean COUNT(*)
The fix
COUNT(column) skips NULLs. If you want the total number of rows regardless of NULLs, use COUNT(*).
The mistake
Selecting non-aggregated columns alongside aggregates without GROUP BY
The fix
You cannot mix plain columns with aggregates unless you GROUP BY those columns. The database will error or return unpredictable results.
Practice Aggregation Functions
18 exercises
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.