📈 Lesson 9: Aggregate Functions & GROUP BY

What Are Aggregate Functions?

A regular SELECT returns one row per matching row in the table. An aggregate function takes multiple rows and collapses them into a single value — a count, a sum, an average, a minimum, or a maximum.

MySQL provides five core aggregate functions:

COUNT — Counting Rows

COUNT is the most commonly used aggregate. It comes in two forms:

COUNT(*) — Count All Rows

COUNT(*) counts every row, including rows with NULL values:

-- How many books do we have?
SELECT COUNT(*) AS total_books FROM books;
                

+-------------+
| total_books |
+-------------+
|          10 |
+-------------+
                    

-- How many customers?
SELECT COUNT(*) AS total_customers FROM customers;
                

+-----------------+
| total_customers |
+-----------------+
|               8 |
+-----------------+
                    

COUNT(column) — Count Non-NULL Values

COUNT(column) only counts rows where that column is not NULL:

-- How many customers have a city listed?
SELECT COUNT(city) AS customers_with_city FROM customers;
                

+---------------------+
| customers_with_city |
+---------------------+
|                   6 |
+---------------------+
                    

We have 8 customers total, but only 6 have a city — 2 have NULL cities (Eve and Hank from our earlier lessons).

COUNT(DISTINCT column) — Count Unique Values

Add DISTINCT to count only unique (non-duplicate) values:

-- How many different authors do we have?
SELECT COUNT(DISTINCT author) AS unique_authors FROM books;
                

+----------------+
| unique_authors |
+----------------+
|             10 |
+----------------+
                    

-- How many different cities are represented?
SELECT COUNT(DISTINCT city) AS unique_cities FROM customers;
                

+---------------+
| unique_cities |
+---------------+
|             4 |
+---------------+
                    

COUNT with WHERE

Combine COUNT with WHERE to count a subset:

-- How many books are in stock?
SELECT COUNT(*) AS in_stock_count FROM books
WHERE in_stock = TRUE;

-- How many books cost more than $10?
SELECT COUNT(*) AS expensive_books FROM books
WHERE price > 10;

-- How many books were published after 1950?
SELECT COUNT(*) AS modern_books FROM books
WHERE published > '1950-01-01';
                

SUM & AVG — Totals and Averages

SUM — Adding Up Values

SUM adds all values in a column. It works with numeric columns:

-- Total value of all books (if you bought one of each)
SELECT SUM(price) AS total_value FROM books;
                

+-------------+
| total_value |
+-------------+
|      109.90 |
+-------------+
                    

-- Total pages across all books
SELECT SUM(pages) AS total_pages FROM books;

-- Total value of in-stock books only
SELECT SUM(price) AS in_stock_value FROM books
WHERE in_stock = TRUE;
                

AVG — Calculating Averages

AVG returns the arithmetic mean (sum ÷ count):

-- Average book price
SELECT AVG(price) AS avg_price FROM books;
                

+-----------+
| avg_price |
+-----------+
| 10.990000 |
+-----------+
                    

That's a lot of decimal places! Use ROUND to clean it up:

-- Average price, rounded to 2 decimal places
SELECT ROUND(AVG(price), 2) AS avg_price FROM books;
                

+-----------+
| avg_price |
+-----------+
|     10.99 |
+-----------+
                    

-- Average page count
SELECT ROUND(AVG(pages)) AS avg_pages FROM books;

-- Average price of in-stock books
SELECT ROUND(AVG(price), 2) AS avg_in_stock_price FROM books
WHERE in_stock = TRUE;
                

Multiple Aggregates in One Query

You can use multiple aggregate functions in a single SELECT:

-- Get a complete price summary
SELECT
    COUNT(*) AS total_books,
    ROUND(SUM(price), 2) AS total_value,
    ROUND(AVG(price), 2) AS avg_price,
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive
FROM books;
                

+-------------+-------------+-----------+----------+----------------+
| total_books | total_value | avg_price | cheapest | most_expensive |
+-------------+-------------+-----------+----------+----------------+
|          10 |      109.90 |     10.99 |     7.99 |          15.99 |
+-------------+-------------+-----------+----------+----------------+
                    

MIN & MAX — Extremes

MIN and MAX find the smallest and largest values. They work with numbers, text, and dates:

With Numbers

-- Cheapest and most expensive books
SELECT
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive
FROM books;
                

+----------+----------------+
| cheapest | most_expensive |
+----------+----------------+
|     7.99 |          15.99 |
+----------+----------------+
                    

With Dates

-- Oldest and newest books
SELECT
    MIN(published) AS oldest_published,
    MAX(published) AS newest_published
FROM books;
                

+------------------+------------------+
| oldest_published | newest_published |
+------------------+------------------+
| 1813-01-28       | 1965-08-01       |
+------------------+------------------+
                    

With Text

For text, MIN returns the first alphabetically and MAX returns the last:

-- First and last book titles alphabetically
SELECT
    MIN(title) AS first_alpha,
    MAX(title) AS last_alpha
FROM books;
                

+-------------+---------------------+
| first_alpha | last_alpha          |
+-------------+---------------------+
| 1984        | To Kill a Mockingbird |
+-------------+---------------------+
                    

-- Which book is cheapest? (full row info)
SELECT title, price FROM books
ORDER BY price ASC LIMIT 1;
                    

Aggregates and NULL

This is one of the most important (and most misunderstood) behaviors in SQL. Here's the rule:

Let's see what this means in practice:

-- COUNT(*) vs COUNT(city) on the customers table
SELECT
    COUNT(*) AS total_rows,
    COUNT(city) AS rows_with_city
FROM customers;
                

+------------+----------------+
| total_rows | rows_with_city |
+------------+----------------+
|          8 |              6 |
+------------+----------------+
                    

COUNT(*) says 8 because it counts all rows. COUNT(city) says 6 because it skips the 2 rows where city is NULL.

AVG and NULL — A Subtle Gotcha

Because AVG ignores NULLs, the average only considers non-NULL values:

-- Imagine a ratings column where some books haven't been rated yet:
-- Book A: 5, Book B: 3, Book C: NULL, Book D: 4

-- AVG(rating) = (5 + 3 + 4) / 3 = 4.0  (NOT divided by 4!)
-- The NULL is skipped entirely — both from the sum AND the count
                

-- Treat NULL ratings as 0
SELECT AVG(IFNULL(rating, 0)) AS avg_with_zeros FROM books;

-- COALESCE does the same thing
SELECT AVG(COALESCE(rating, 0)) AS avg_with_zeros FROM books;
                    

GROUP BY — Per-Category Summaries

Without GROUP BY, aggregate functions summarize the entire table. With GROUP BY, they summarize each group separately.

Basic GROUP BY

-- How many books does each author have?
SELECT author, COUNT(*) AS book_count
FROM books
GROUP BY author;
                

+----------------------+------------+
| author               | book_count |
+----------------------+------------+
| Aldous Huxley        |          1 |
| F. Scott Fitzgerald  |          1 |
| Frank Herbert        |          1 |
| George Orwell        |          1 |
| Harper Lee           |          1 |
| J.D. Salinger        |          1 |
| J.R.R. Tolkien       |          1 |
| Jane Austen          |          1 |
| Ray Bradbury         |          1 |
| J.R.R. Tolkien       |          1 |
+----------------------+------------+
                    

-- How many customers are in each city?
SELECT city, COUNT(*) AS customer_count
FROM customers
WHERE city IS NOT NULL
GROUP BY city;
                

+-------------+----------------+
| city        | customer_count |
+-------------+----------------+
| Chicago     |              2 |
| Houston     |              1 |
| Los Angeles |              1 |
| New York    |              2 |
+-------------+----------------+
                    

GROUP BY with Multiple Aggregates

-- Price stats by in_stock status
SELECT
    in_stock,
    COUNT(*) AS book_count,
    ROUND(AVG(price), 2) AS avg_price,
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive
FROM books
GROUP BY in_stock;
                

+----------+------------+-----------+----------+----------------+
| in_stock | book_count | avg_price | cheapest | most_expensive |
+----------+------------+-----------+----------+----------------+
|        0 |          3 |     10.49 |     8.99 |          11.99 |
|        1 |          7 |     11.13 |     7.99 |          15.99 |
+----------+------------+-----------+----------+----------------+
                    

GROUP BY with ORDER BY

You can sort the grouped results — often you'll sort by the aggregate value:

-- Cities with the most customers first
SELECT city, COUNT(*) AS customer_count
FROM customers
WHERE city IS NOT NULL
GROUP BY city
ORDER BY customer_count DESC;
                

-- Authors ranked by average book price
SELECT
    author,
    COUNT(*) AS books,
    ROUND(AVG(price), 2) AS avg_price
FROM books
GROUP BY author
ORDER BY avg_price DESC;
                

GROUP BY with LIMIT

-- Top 3 cities by customer count
SELECT city, COUNT(*) AS customer_count
FROM customers
WHERE city IS NOT NULL
GROUP BY city
ORDER BY customer_count DESC
LIMIT 3;
                

Grouping by Multiple Columns

You can group by more than one column for finer-grained categories:

-- Count books by in_stock status AND price range
SELECT
    in_stock,
    CASE
        WHEN price < 10 THEN 'Under $10'
        WHEN price < 15 THEN '$10-$15'
        ELSE '$15+'
    END AS price_range,
    COUNT(*) AS book_count
FROM books
GROUP BY in_stock, price_range
ORDER BY in_stock DESC, price_range;
                

HAVING — Filtering Groups

WHERE filters individual rows before grouping. HAVING filters groups after grouping. This is one of the most important distinctions in SQL.

Basic HAVING

-- Show only cities with MORE than 1 customer
SELECT city, COUNT(*) AS customer_count
FROM customers
WHERE city IS NOT NULL
GROUP BY city
HAVING customer_count > 1;
                

+----------+----------------+
| city     | customer_count |
+----------+----------------+
| Chicago  |              2 |
| New York |              2 |
+----------+----------------+
                    

Houston (1 customer) and Los Angeles (1 customer) are excluded because they don't satisfy HAVING customer_count > 1.

WHERE vs HAVING

This is a critical distinction:

-- ⛔ WRONG — you can't use an aggregate in WHERE
SELECT city, COUNT(*) AS customer_count
FROM customers
WHERE COUNT(*) > 1   -- ERROR! Aggregates not allowed in WHERE
GROUP BY city;

-- ✅ CORRECT — use HAVING for aggregate conditions
SELECT city, COUNT(*) AS customer_count
FROM customers
GROUP BY city
HAVING COUNT(*) > 1;
                

Using WHERE and HAVING Together

Often you need both — WHERE to filter rows before grouping, and HAVING to filter groups after:

-- Among in-stock books, find price ranges with more than 2 books
SELECT
    CASE
        WHEN price < 10 THEN 'Under $10'
        WHEN price < 13 THEN '$10-$13'
        ELSE '$13+'
    END AS price_range,
    COUNT(*) AS book_count,
    ROUND(AVG(price), 2) AS avg_price
FROM books
WHERE in_stock = TRUE          -- filter rows FIRST
GROUP BY price_range
HAVING book_count > 2          -- then filter groups
ORDER BY avg_price;
                

The execution order is: WHERE removes out-of-stock books → GROUP BY groups the remaining books by price range → HAVING removes groups with 2 or fewer books.

More HAVING Examples

-- Authors whose average book price is above $12
SELECT
    author,
    ROUND(AVG(price), 2) AS avg_price
FROM books
GROUP BY author
HAVING avg_price > 12
ORDER BY avg_price DESC;

-- Cities where total customer spending would exceed $100
-- (hypothetical example with an orders table)
-- SELECT city, SUM(order_total) AS total_spent
-- FROM customers JOIN orders ON customers.id = orders.customer_id
-- GROUP BY city
-- HAVING total_spent > 100;
                

Updated Clause Order

With GROUP BY and HAVING added, the full clause order becomes:

-- The full pattern in action:
SELECT city, COUNT(*) AS customer_count
FROM customers
WHERE city IS NOT NULL         -- 1. Filter rows
GROUP BY city                  -- 2. Group remaining rows
HAVING customer_count > 1      -- 3. Filter groups
ORDER BY customer_count DESC   -- 4. Sort results
LIMIT 5;                       -- 5. Limit output
                

Common Mistakes

Mistake 1: Non-Aggregated Columns Without GROUP BY

-- ⛔ WRONG — title is not aggregated or grouped
SELECT title, COUNT(*) FROM books;

-- ✅ CORRECT — group by the non-aggregated column
SELECT title, COUNT(*) AS count FROM books
GROUP BY title;

-- ✅ Also correct — aggregate everything, no group needed
SELECT COUNT(*) AS total FROM books;
                

Mistake 2: Using Aggregates in WHERE

-- ⛔ WRONG — WHERE can't use aggregates
SELECT author FROM books
WHERE COUNT(*) > 1
GROUP BY author;

-- ✅ CORRECT — use HAVING
SELECT author, COUNT(*) AS book_count FROM books
GROUP BY author
HAVING book_count > 1;
                

Mistake 3: Forgetting That AVG Ignores NULL

-- If you have ratings: 5, 3, NULL, 4
-- AVG(rating) = 4.0 (divides by 3, not 4)
-- This might not be what you want!

-- If NULLs should count as zero:
SELECT AVG(IFNULL(rating, 0)) AS avg_rating FROM books;
                

Mistake 4: Wrong Clause Order

-- ⛔ WRONG — HAVING before GROUP BY
SELECT city, COUNT(*) FROM customers
HAVING COUNT(*) > 1
GROUP BY city;

-- ✅ CORRECT
SELECT city, COUNT(*) FROM customers
GROUP BY city
HAVING COUNT(*) > 1;
                

Exercises

-- 1
SELECT COUNT(*) AS total_books FROM books;

-- 2
SELECT ROUND(AVG(price), 2) AS avg_price FROM books;

-- 3
SELECT SUM(pages) AS total_pages FROM books;

-- 4
SELECT
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive
FROM books;

-- 5
SELECT COUNT(*) AS in_stock_count FROM books
WHERE in_stock = TRUE;
                        

-- 1
SELECT city, COUNT(*) AS customer_count
FROM customers
WHERE city IS NOT NULL
GROUP BY city;

-- 2
SELECT
    in_stock,
    COUNT(*) AS book_count,
    ROUND(AVG(price), 2) AS avg_price
FROM books
GROUP BY in_stock;

-- 3
SELECT city, COUNT(*) AS customer_count
FROM customers
WHERE city IS NOT NULL
GROUP BY city
ORDER BY customer_count DESC;
                        

-- 1
SELECT city, COUNT(*) AS customer_count
FROM customers
WHERE city IS NOT NULL
GROUP BY city
HAVING customer_count > 1;

-- 2
SELECT
    CASE
        WHEN price < 10 THEN 'Under $10'
        WHEN price < 15 THEN '$10-$15'
        ELSE '$15+'
    END AS price_range,
    COUNT(*) AS book_count
FROM books
WHERE in_stock = TRUE
GROUP BY price_range
HAVING book_count >= 2;

-- 3
SELECT
    author,
    ROUND(AVG(pages)) AS avg_pages
FROM books
GROUP BY author
HAVING avg_pages > 250;
                        

SELECT
    author,
    COUNT(*) AS book_count,
    ROUND(AVG(price), 2) AS avg_price,
    SUM(pages) AS total_pages
FROM books
WHERE in_stock = TRUE
GROUP BY author
HAVING avg_price > 10
ORDER BY avg_price DESC
LIMIT 5;
                        

Summary

Quick Reference

📚 Additional Resources

• MySQL Docs — Aggregate Functions
• MySQL Docs — GROUP BY Handling
• MySQL Docs — GROUP BY Modifiers

🚀 What's Next?

You can now summarize, group, and filter aggregate data — powerful tools for turning raw rows into meaningful insights. But what if you need a query inside another query? "Show me all books that cost more than the average price" — that requires knowing the average first, then using it as a filter. In the next lesson, you'll learn subqueries — queries nested inside other queries — to solve exactly these kinds of multi-step problems.