Chapter 10: Aggregate Functions
Up until now, we’ve been looking at individual rows — one book, one author, one order at a time. Now we’re going to summarize huge amounts of data:
- How many books do we have in total?
- What’s the average price?
- Who is the most expensive author?
- How many books per author?
The magic words here are aggregate functions + GROUP BY + HAVING.
We’ll cover:
- COUNT, SUM, AVG, MIN, MAX — the main aggregate functions
- GROUP BY — group rows that have the same values
- HAVING — filter groups (like WHERE, but for after grouping)
We’ll use our familiar bookshop database with the books and authors tables.
Sample Data Reminder (books table – let’s add a few more rows for better examples)
| book_id | title | author_id | price | stock | category |
|---|---|---|---|---|---|
| 1 | The Room on the Roof | 1 | 250.00 | 45 | Fiction |
| 2 | 2 States | 3 | 299.00 | 120 | Fiction |
| 3 | The White Tiger | 2 | 399.00 | 35 | Fiction |
| 4 | Wise and Otherwise | 5 | 199.00 | 80 | Non-Fiction |
| 5 | The Immortals of Meluha | 4 | 349.00 | 65 | Fantasy |
| 6 | The Blue Umbrella | 1 | 180.00 | 50 | Children |
| 7 | Malgudi Days | 6 | 220.00 | 90 | Fiction |
| 8 | Train to Pakistan | 7 | 280.00 | 60 | Historical |
| 9 | Half Girlfriend | 3 | 280.00 | 95 | Fiction |
| 10 | The Palace of Illusions | 8 | 450.00 | 25 | Mythology |
(Assume we have 8 authors with IDs 1 to 8)
1. The Main Aggregate Functions
These functions summarize multiple rows into one single value.
| Function | What it does | Ignores NULL? | Example Usage |
|---|---|---|---|
| COUNT() | Counts number of rows (or non-NULL values) | Yes | How many books? How many authors? |
| SUM() | Adds up all values | Yes | Total stock, total revenue |
| AVG() | Average (mean) of values | Yes | Average book price |
| MIN() | Smallest value | Yes | Cheapest book |
| MAX() | Largest value | Yes | Most expensive book |
Important: When you use an aggregate function without GROUP BY, it gives one result for the entire table.
Examples (no GROUP BY yet):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
-- Total number of books SELECT COUNT(*) AS total_books FROM books; -- Result: 10 -- Number of books that have a price (ignores NULL prices) SELECT COUNT(price) AS priced_books FROM books; -- Total stock across all books SELECT SUM(stock) AS total_stock FROM books; -- Result: e.g., 665 -- Average book price SELECT AVG(price) AS average_price FROM books; -- Result: e.g., 290.60 -- Cheapest and most expensive book SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive FROM books; -- Result: cheapest = 180.00, most_expensive = 450.00 |
Pro Tip: COUNT(*) counts all rows (including NULLs). COUNT(column) counts only non-NULL values in that column.
2. GROUP BY – Grouping Rows Before Aggregating
This is the game-changer! GROUP BY tells SQL: “Group rows that have the same value in this column, then apply aggregates to each group.”
Syntax:
|
0 1 2 3 4 5 6 7 8 |
SELECT column_to_group_by, AGGREGATE_FUNCTION(...) FROM table GROUP BY column_to_group_by; |
Real examples:
|
0 1 2 3 4 5 6 7 8 9 10 11 |
-- Number of books per author SELECT author_id, COUNT(*) AS number_of_books FROM books GROUP BY author_id; |
Result might look like:
| author_id | number_of_books |
|---|---|
| 1 | 2 |
| 2 | 1 |
| 3 | 2 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
| 8 | 1 |
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- Total stock and average price per category SELECT category, COUNT(*) AS books_in_category, SUM(stock) AS total_stock, AVG(price) AS avg_price, MIN(price) AS cheapest_in_cat, MAX(price) AS most_expensive_in_cat FROM books GROUP BY category; |
Even better – join with authors for names:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT a.name AS author_name, COUNT(b.book_id) AS books_written, SUM(b.stock) AS total_stock, ROUND(AVG(b.price), 2) AS average_price FROM books b INNER JOIN authors a ON b.author_id = a.author_id GROUP BY a.author_id, a.name ORDER BY books_written DESC; |
Important Rule: Any column in SELECT that is not inside an aggregate function must appear in GROUP BY.
Wrong:
|
0 1 2 3 4 5 6 7 8 |
SELECT author_id, title, COUNT(*) -- ERROR! FROM books GROUP BY author_id; |
Correct:
|
0 1 2 3 4 5 6 7 8 |
SELECT author_id, COUNT(*) FROM books GROUP BY author_id; |
3. HAVING Clause – Filtering Groups (WHERE for Aggregates)
WHERE filters individual rows before grouping. HAVING filters groups after grouping.
Syntax:
|
0 1 2 3 4 5 6 7 8 9 |
SELECT ... FROM ... GROUP BY ... HAVING condition_on_aggregate; |
Examples:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
-- Authors who have written more than 1 book SELECT author_id, COUNT(*) AS books_count FROM books GROUP BY author_id HAVING COUNT(*) > 1; |
Result: only author_id 1 and 3 (they have 2 books each)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Categories with average price > ₹300 and at least 2 books SELECT category, AVG(price) AS avg_price, COUNT(*) AS book_count FROM books GROUP BY category HAVING AVG(price) > 300.00 AND COUNT(*) >= 2; |
Very common pattern – combine WHERE + GROUP BY + HAVING:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- Indian authors with total stock > 100 and average price between 200-350 SELECT a.name, COUNT(b.book_id) AS books, SUM(b.stock) AS total_stock, ROUND(AVG(b.price), 2) AS avg_price FROM books b INNER JOIN authors a ON b.author_id = a.author_id WHERE a.country = 'India' GROUP BY a.author_id, a.name HAVING SUM(b.stock) > 100 AND AVG(b.price) BETWEEN 200 AND 350 ORDER BY total_stock DESC; |
Full Realistic Report Example
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-- Sales manager's favorite report: Books performance by author SELECT a.name AS author, COUNT(b.book_id) AS books_published, SUM(b.stock) AS current_stock, ROUND(AVG(b.price), 2) AS avg_price, MIN(b.price) AS cheapest_book, MAX(b.price) AS most_expensive_book FROM books b INNER JOIN authors a ON b.author_id = a.author_id GROUP BY a.author_id, a.name HAVING COUNT(b.book_id) >= 1 -- at least one book ORDER BY current_stock DESC, avg_price DESC; |
