Aggregate functions take many rows and collapse them into a single summary value — total, average, count, min, max. Instead of looking at each row individually, you're asking a question about the whole set.

We'll keep using the books table:

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255),
    genre VARCHAR(100),
    price DECIMAL(6,2),
    published_year INT,
    in_stock BOOLEAN DEFAULT TRUE
);

INSERT INTO books (title, author, genre, price, published_year, in_stock) VALUES
('Dune', 'Frank Herbert', 'Sci-Fi', 15.50, 1965, TRUE),
('1984', 'George Orwell', 'Dystopian', 9.99, 1949, TRUE),
('Animal Farm', 'George Orwell', 'Satire', 7.50, 1945, FALSE),
('Brave New World', 'Aldous Huxley', 'Dystopian', 10.25, 1932, TRUE),
('Foundation', 'Isaac Asimov', 'Sci-Fi', 12.00, 1951, FALSE);

mysql> SELECT * FROM books;
+----+-----------------+---------------+-----------+-------+----------------+----------+
| id | title           | author        | genre     | price | published_year | in_stock |
+----+-----------------+---------------+-----------+-------+----------------+----------+
|  1 | Dune            | Frank Herbert | Sci-Fi    | 15.50 |           1965 |        1 |
|  2 | 1984            | George Orwell | Dystopian |  9.99 |           1949 |        1 |
|  3 | Animal Farm     | George Orwell | Satire    |  7.50 |           1945 |        0 |
|  4 | Brave New World | Aldous Huxley | Dystopian | 10.25 |           1932 |        1 |
|  5 | Foundation      | Isaac Asimov  | Sci-Fi    | 12.00 |           1951 |        0 |
+----+-----------------+---------------+-----------+-------+----------------+----------+

SELECT COUNT(*) FROM books;          -- how many rows: 5
SELECT SUM(price) FROM books;        -- total of all prices
SELECT AVG(price) FROM books;        -- average price
SELECT MIN(price) FROM books;        -- cheapest price
SELECT MAX(price) FROM books;        -- most expensive price

Each of these returns one row, not one per book — they summarize the whole table.

GROUP BY splits rows into buckets first, then runs the aggregate on each bucket separately.

Average price per genre:

SELECT genre, AVG(price) AS avg_price
FROM books
GROUP BY genre;

Result: one row per genre, each with its own average — not one average for everything.

+-----------+-----------+
| genre     | avg_price |
+-----------+-----------+
| Sci-Fi    | 13.750000 |
| Dystopian | 10.120000 |
| Satire    |  7.500000 |
+-----------+-----------+

How many books per author:

SELECT author, COUNT(*) AS book_count
FROM books
GROUP BY author;

+---------------+------------+
| author        | book_count |
+---------------+------------+
| Frank Herbert |          1 |
| George Orwell |          2 |
| Aldous Huxley |          1 |
| Isaac Asimov  |          1 |
+---------------+------------+

Key rule: every column in SELECT must either be inside an aggregate function, or listed in GROUP BY. This fails:

SELECT genre, price, AVG(price) FROM books GROUP BY genre;
-- ERROR — price isn't aggregated or grouped

WHERE filters rows before grouping. HAVING filters the groups themselves, after aggregation.

Genres with more than 1 book:

SELECT genre, COUNT(*) AS book_count
FROM books
GROUP BY genre
HAVING COUNT(*) > 1;

+-----------+------------+
| genre     | book_count |
+-----------+------------+
| Sci-Fi    |          2 |
| Dystopian |          2 |
+-----------+------------+

You can't write WHERE COUNT(*) >= 1 — COUNT(*) doesn't exist yet at the point WHERE runs.

Among in-stock books, show genres averaging over $10:

SELECT genre, AVG(price) AS avg_price
FROM books
WHERE in_stock = TRUE
GROUP BY genre
HAVING AVG(price) > 10;

+-----------+-----------+
| genre     | avg_price |
+-----------+-----------+
| Sci-Fi    | 15.500000 |
| Dystopian | 10.120000 |
+-----------+-----------+

Order matters: WHERE filters rows first → then GROUP BY buckets them → then HAVING filters the buckets → then results are returned.