Lesson 7 • Intermediate
Aggregate Functions
Calculate totals, averages, counts, and extremes to transform raw data into meaningful summaries.
🎯 What You'll Learn
- COUNT rows and unique values
- SUM and AVG for numerical analysis
- MIN and MAX for extremes
- Combine multiple aggregates in one query
- How aggregates handle NULL values
What Are Aggregate Functions?
Aggregate functions take many rows and return a single value. Instead of seeing every row, you get a summary — total revenue, average rating, highest price.
📊 Real-World Analogy
Aggregate functions are like a calculator for your database. You have 10,000 orders — SUM(total) adds them all up, AVG(total) gives you the average, COUNT(*) tells you how many there are.
| Function | Returns | NULLs |
|---|---|---|
| COUNT(*) | Number of rows | Counts all rows |
| COUNT(col) | Non-NULL values | Skips NULLs |
| SUM(col) | Total of all values | Skips NULLs |
| AVG(col) | Average value | Skips NULLs |
| MIN(col) | Smallest value | Skips NULLs |
| MAX(col) | Largest value | Skips NULLs |
COUNT — Counting Rows
Count total rows, non-NULL values, and unique values
-- COUNT: how many rows?
SELECT COUNT(*) AS total_orders FROM orders;
-- COUNT non-NULL values only
SELECT COUNT(email) AS has_email FROM customers;
-- COUNT unique values
SELECT COUNT(DISTINCT category) AS num_categories
FROM products;SUM & AVG — Totals and Averages
Add up values and calculate averages
-- SUM: add up values
SELECT SUM(total) AS total_revenue FROM orders;
-- SUM with expression
SELECT SUM(quantity * unit_price) AS order_value
FROM order_items
WHERE order_id = 1001;
-- AVG: calculate the average
SELECT AVG(price) AS avg_price FROM products;
-- AVG ignores NULLs automatically
SELECT AVG(rating) AS avg_rating FROM reviews;MIN & MAX — Finding Extremes
Find the smallest and largest values in any column
-- MIN and MAX
SELECT
MIN(price) AS cheapest,
MAX(price) AS most_expensive
FROM products;
-- Works with dates too!
SELECT
MIN(order_date) AS first_order,
MAX(order_date) AS latest_order
FROM orders;
-- Works with text (alphabetical)
SELECT
MIN(last_name) AS first_alpha,
MAX(last_name) AS last_alpha
FROM employees;Aggregates and NULLs
An important detail: all aggregate functions except COUNT(*) ignore NULL values. This can change your results!
⚠️ Common Mistake
If 100 employees exist but only 80 have a bonus, AVG(bonus) divides by 80, not 100. The average is higher than you might expect! Use AVG(COALESCE(bonus, 0)) to treat NULLs as zero.
💡 Pro Tip
Use ROUND(AVG(price), 2) to round averages to 2 decimal places. Raw averages often have 15+ decimal digits.
Combined Aggregates
Build comprehensive summary reports with multiple aggregates
-- Combine multiple aggregates in one query
SELECT
COUNT(*) AS total_products,
COUNT(DISTINCT category) AS num_categories,
ROUND(AVG(price), 2) AS avg_price,
MIN(price) AS cheapest,
MAX(price) AS most_expensive,
SUM(stock) AS total_inventory
FROM products
WHERE active = true;
-- Aggregate with WHERE filtering
SELECT
COUNT(*) AS electronics_count,
AVG(price) AS avg_electronics_price,
SUM(stock * price) AS total_electronics_value
FROM products
WHERE category
...📘 Quick Reference
| Query | Purpose |
|---|---|
| SELECT COUNT(*) FROM t | Total rows |
| SELECT SUM(col) FROM t | Sum of values |
| ROUND(AVG(col), 2) | Rounded average |
| COUNT(DISTINCT col) | Unique count |
| COALESCE(col, 0) | Treat NULLs as 0 |
🎉 Lesson Complete!
You can now summarize data with aggregate functions. Next, you'll learn GROUP BY — which lets you calculate aggregates per category (revenue per department, orders per customer)!
Sign up for free to track which lessons you've completed and get learning reminders.