SQL Querying and Aggregation: 10 Code-Along Examples

Learn filtering, grouping and summarising by running them. Ten copy-and-run examples covering COUNT, WHERE, text comparison, AND and OR precedence, BETWEEN and IN, NULL, aggregates, GROUP BY, HAVING, and the full pipeline.

The SQL querying and aggregation article covers the point where SQL becomes an analytical tool rather than a way to read a table: how many rows match, what the average is within each group, which categories cross a threshold. All of it rests on a small set of clauses that always appear in the same order, and most of the confusion people have with SQL is really confusion about that order. Every query here was run against a real database and the grid underneath is what came back, including two queries that return the wrong answer on purpose and one that refuses to run at all. This workbook works through the whole range: the three forms of COUNT, row filtering, the string rules, the operator-precedence trap, range and set filters, NULL, the aggregate family, grouping, filtering groups, and the complete pipeline. The idea that ties it together arrives in the last example.

The examples run on SQLite, which needs no server, and use the same books table as the SQL basics workbook:

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY, title TEXT, author TEXT, genre TEXT,
    year INTEGER, pages INTEGER, rating REAL, copies INTEGER
);
INSERT INTO books VALUES
(1,'The Long Cold','Ada Mercer','Thriller',2019,412,4.2,7),
(2,'Salt and Iron','Ada Mercer','Thriller',2021,388,4.5,11),
(3,'Nightjar','Ada Mercer','Horror',2022,304,3.9,4),
(4,'Small Machines','Tomas Ilie','Science Fiction',2018,506,4.7,9),
(5,'The Quiet Grid','Tomas Ilie','Science Fiction',2020,478,4.1,6),
(6,'Harbour Lights','Nia Okoro','Literary',2017,266,4.4,5),
(7,'The Understudy','Nia Okoro','Literary',2023,331,4.6,13),
(8,'Weathering','Nia Okoro','Literary',2015,290,3.8,2),
(9,'Cold Open','Priya Raman','Thriller',2024,357,4.3,8),
(10,'Follow the Wire','Priya Raman','Thriller',2016,401,3.6,3),
(11,'The Bone Orchard','Kit Alvarez','Horror',2021,342,4.0,6),
(12,'Static','Kit Alvarez','Horror',2023,288,4.8,15),
(13,'Fieldwork','Tomas Ilie','Literary',2024,395,NULL,4),
(14,'Second Sun','Ada Mercer','Science Fiction',2025,449,4.4,10),
(15,'The Inventory','Nia Okoro','Thriller',2019,372,3.5,1);

1. The three forms of COUNT

COUNT looks like one function and behaves like three, and the differences between them are a free data-quality report.

SELECT COUNT(*)                AS all_books,
       COUNT(rating)           AS with_rating,
       COUNT(DISTINCT author)  AS authors,
       COUNT(DISTINCT genre)   AS genres
FROM books;

all_books  with_rating  authors  genres
---------  -----------  -------  ------
15         14           5        4
(1 row)

Four numbers, four different questions. COUNT(*) counts rows and does not care what is in them, so a row of nothing but NULLs still counts because the row exists. COUNT(column) counts only rows where that column has a value, which is why 15 books have 14 ratings and you have just found your missing data without writing a data-quality query. COUNT(DISTINCT column) counts different values, which tells you the cardinality of a column, and it is worth running on any new categorical column before you do anything else with it, because five distinct authors is a grouping key and five thousand is an identifier.

2. WHERE, the bouncer at the door

WHERE tests every row and keeps only the ones where the condition is true. Everything else is discarded before any other work happens.

SELECT title, rating
FROM books
WHERE rating > 4.4;

title           rating
--------------  ------
Salt and Iron   4.5
Small Machines  4.7
The Understudy  4.6
Static          4.8
(4 rows)

SELECT title, year
FROM books
WHERE year <> 2019 AND pages < 300;

title           year
--------------  ----
Harbour Lights  2017
Weathering      2015
Static          2023
(3 rows)

All the comparison operators you would expect work here: >, <, >=, <=, =, and <> for not-equal, with != accepted by most engines as a synonym. The important thing about WHERE is not its syntax but its position in the pipeline, because it runs early, before grouping and before the SELECT list is evaluated. That is what makes it the cheapest place to reduce your data, and on a warehouse that bills by bytes scanned it is often the difference between a query that costs pennies and one that costs real money. It also means an alias you create in SELECT cannot be used in WHERE, since it does not exist yet.

3. Comparing text

Filtering strings comes with three rules, and two of them catch people who are coming from another language.

SELECT title, genre
FROM books
WHERE genre = 'Horror';

title             genre
----------------  ------
Nightjar          Horror
The Bone Orchard  Horror
Static            Horror
(3 rows)

SELECT COUNT(*) AS n
FROM books
WHERE genre = 'horror';

n
-
0
(1 row)

Same query, lowercase value, zero rows. String comparison is case-sensitive here as it is in PostgreSQL and BigQuery, while MySQL’s default collation is not, and the safe assumption when you do not know the engine is that case matters. The other two rules are that strings go in single quotes, because double quotes mean an identifier such as a column name in standard SQL, and that equality is a single = rather than the == you would write in Python or JavaScript. When you want case-insensitive matching, wrap both sides in LOWER() or use the engine’s case-insensitive operator, but do it deliberately rather than hoping.

4. AND, OR, and the precedence trap

Mixing AND and OR without parentheses produces a query that runs perfectly and answers a different question.

SELECT title, year, genre
FROM books
WHERE genre = 'Literary' OR genre = 'Thriller' AND year >= 2021;

title           year  genre
--------------  ----  --------
Salt and Iron   2021  Thriller
Harbour Lights  2017  Literary
The Understudy  2023  Literary
Weathering      2015  Literary
Cold Open       2024  Thriller
Fieldwork       2024  Literary
(6 rows)

SELECT title, year, genre
FROM books
WHERE (genre = 'Literary' OR genre = 'Thriller') AND year >= 2021;

title           year  genre
--------------  ----  --------
Salt and Iron   2021  Thriller
The Understudy  2023  Literary
Cold Open       2024  Thriller
Fieldwork       2024  Literary
(4 rows)

The first query was meant to say “literary or thriller books from 2021 onwards” and returned a book from 2015. ANDbinds tighter than OR, so the database read it as “literary books, or thrillers from 2021 onwards”, and the year filter never applied to the literary arm at all. Nothing errors, nothing warns, and the result is plausible enough to go into a report. The rule that prevents this costs nothing: any time AND and OR appear in the same WHERE clause, put parentheses round each OR group, even when you are confident about the precedence, because the next person to read the query will not be.

5. BETWEEN and IN

Two shortcuts make range and set filters readable, and both have a detail worth knowing.

SELECT title, year
FROM books
WHERE year BETWEEN 2019 AND 2021
ORDER BY year;

title             year
----------------  ----
The Long Cold     2019
The Inventory     2019
The Quiet Grid    2020
Salt and Iron     2021
The Bone Orchard  2021
(5 rows)

SELECT title, genre
FROM books
WHERE genre IN ('Horror', 'Literary')
ORDER BY genre;

title             genre
----------------  --------
Nightjar          Horror
The Bone Orchard  Horror
Static            Horror
Harbour Lights    Literary
The Understudy    Literary
Weathering        Literary
Fieldwork         Literary
(7 rows)

BETWEEN is inclusive at both ends, which is the detail to remember, since both 2019 and 2021 appear in the output. That makes it the readable equivalent of year >= 2019 AND year <= 2021 and a genuine trap on dates and timestamps, where BETWEEN '2026-01-01' AND '2026-01-31' silently excludes almost all of the 31st because midnight is where that day starts. INreplaces a chain of OR comparisons and stays readable as the list grows, with NOT IN as its negation, though NOT INdeserves care when the column can be NULL, because a NULL in the list makes the whole condition return no rows at all.

6. NULL is not a value

A NULL is the absence of a value, so it fails comparisons rather than passing them, including the ones you expected it to pass.

SELECT COUNT(*) AS n FROM books WHERE rating <> 4.2;

n
--
13
(1 row)

SELECT COUNT(*) AS n FROM books WHERE rating <> 4.2 OR rating IS NULL;

n
--
14
(1 row)

Fifteen books, one of them rated 4.2, so “not rated 4.2” should be fourteen and the first query says thirteen. The missing row is the book with no rating, which was excluded because NULL <> 4.2 is not true, it is unknown, and WHERE keeps only what is definitely true. This is the most common way real reports lose rows, and it happens on the negative filters people trust most. Testing for absence needs IS NULL or IS NOT NULL, never = NULL, and when you want the unknowns included in a negative filter you have to say so explicitly, as the second query does. LIKE 'The %' is worth knowing alongside these for pattern matching, where % is any run of characters and _ is exactly one.

7. The aggregate family

Five functions collapse many rows into one number, and one of them treats NULL in a way that matters.

SELECT COUNT(*)              AS books,
       SUM(copies)           AS total_copies,
       ROUND(AVG(rating), 2) AS avg_rating,
       MIN(year)             AS earliest,
       MAX(pages)            AS longest
FROM books;

books  total_copies  avg_rating  earliest  longest
-----  ------------  ----------  --------  -------
15     104           4.2         2015      506
(1 row)

SELECT ROUND(AVG(rating), 3)               AS avg_rating,
       ROUND(SUM(rating) / COUNT(*), 3)    AS avg_if_null_were_zero
FROM books;

avg_rating  avg_if_null_were_zero
----------  ---------------------
4.2         3.92
(1 row)

The second query is the one to take away. AVG ignores NULLs entirely, dividing the sum by the count of non-null values, so it returns 4.2 across the fourteen books that have a rating. Dividing the sum by COUNT(*) instead treats the missing rating as a zero and produces 3.92, which is wrong in a specific and dangerous way: it looks like a rating, it is in the right range, and it is quietly punishing a book for not having been rated. SUM, MIN and MAX skip NULLs on the same principle. Whether that is the behaviour you want is a decision, and the point is to make it knowingly.

8. GROUP BY

GROUP BY bundles rows into buckets and runs the aggregates once per bucket instead of once per table.

SELECT genre,
       COUNT(*)              AS titles,
       ROUND(AVG(rating), 2) AS avg_rating,
       SUM(copies)           AS copies
FROM books
GROUP BY genre
ORDER BY titles DESC;

genre            titles  avg_rating  copies
---------------  ------  ----------  ------
Thriller         5       4.02        30
Literary         4       4.27        24
Science Fiction  3       4.4         25
Horror           3       4.23        25
(4 rows)

SELECT genre, author, COUNT(*) AS titles
FROM books
GROUP BY genre, author
ORDER BY genre, titles DESC;

genre            author       titles
---------------  -----------  ------
Horror           Kit Alvarez  2
Horror           Ada Mercer   1
Literary         Nia Okoro    3
Literary         Tomas Ilie   1
Science Fiction  Tomas Ilie   2
Science Fiction  Ada Mercer   1
Thriller         Priya Raman  2
Thriller         Ada Mercer   2
Thriller         Nia Okoro    1
(9 rows)

One row per group is the rule, and grouping by two columns gives one row per combination that actually exists in the data. The constraint that follows is the one beginners fight: every column in your SELECT must either be in the GROUP BYor wrapped in an aggregate, because a group of five thriller rows has five titles and no single one to show. SQLite is unusually permissive here and will let you select an ungrouped column, picking an arbitrary row’s value with no warning, while PostgreSQL and BigQuery reject the query outright. Do not rely on the permissive behaviour, because the value it returns is genuinely arbitrary and the query will break the day it moves to another engine.

9. HAVING filters groups, WHERE filters rows

The two filtering clauses look similar and operate on completely different things.

SELECT author, COUNT(*) AS titles, ROUND(AVG(rating), 2) AS avg_rating
FROM books
GROUP BY author
HAVING COUNT(*) >= 3
ORDER BY avg_rating DESC;

author      titles  avg_rating
----------  ------  ----------
Tomas Ilie  3       4.4
Ada Mercer  4       4.25
Nia Okoro   4       4.08
(3 rows)

SELECT author, COUNT(*) AS titles
FROM books
WHERE COUNT(*) >= 3
GROUP BY author;

OperationalError: misuse of aggregate: COUNT()

The error is the lesson. WHERE runs before the rows have been grouped, so at that moment there is no such thing as COUNT(*) for an author, and the database has nothing to evaluate. HAVING runs after grouping, when each author is one row with a count attached, which is why the same condition works there. The rule to carry: filter rows with WHERE, filter groups with HAVING, and if a condition mentions an aggregate it belongs in HAVING. Doing as much as possible in WHEREis also the faster habit, since rows removed before grouping never have to be grouped at all.

10. The full pipeline, and the model

Every clause in this workbook, in the order they are written.

SELECT genre,
       COUNT(*)              AS titles,
       ROUND(AVG(pages), 0)  AS avg_pages,
       ROUND(AVG(rating), 2) AS avg_rating
FROM books
WHERE year >= 2018
GROUP BY genre
HAVING COUNT(*) > 1
ORDER BY avg_rating DESC
LIMIT 3;

genre            titles  avg_pages  avg_rating
---------------  ------  ---------  ----------
Literary         2       363.0      4.6
Science Fiction  3       478.0      4.4
Horror           3       311.0      4.23
(3 rows)

The model that ties the ten together is that you write a query in one order and the database runs it in another, and almost every surprising result in this workbook comes from that gap. The written order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. The execution order is FROM first, to find the data; then WHERE, throwing away rows one at a time; then GROUP BY, collapsing survivors into buckets; then HAVING, throwing away whole buckets; then SELECT, which is where aggregates are computed and aliases are born; then ORDER BY, arranging what remains; and LIMIT last, taking the first few. Read that list once and four things stop being arbitrary. WHERE cannot see an aggregate because aggregates do not exist yet. HAVING can, because they do. WHERE cannot use an alias from SELECT while ORDER BY can, for the same reason. And filtering in WHERE is cheaper than filtering in HAVING because it happens before the expensive part rather than after it.

Work through these and you have the article in practice: three counts that double as a data-quality check; WHERE and its position in the pipeline; single quotes, single equals, and case sensitivity; the missing parentheses that let a 2015 book through a 2021 filter; inclusive BETWEEN and set-based IN; the NULL that silently vanishes from a not-equal filter; AVGignoring NULLs where a manual division would not; GROUP BY and the rule about ungrouped columns; the aggregate in WHERE that will not run; and the full pipeline with its execution order. The habit that follows is a diagnostic one: when a query returns fewer rows than you expected, look for a NULL in the filtered column first, and when it returns more, look for a missing pair of parentheses.

Thanks for reading, Andrei.