Grouping and Filtering

The previous lesson built aggregate functions, but every one of them reduced the whole table to a single result. In practice that is usually not what is wanted: the question “how many books in total” is asked once and answered once, while “how many books does each author have” gets asked over and over.

This lesson builds the clause that splits rows into sets and produces a separate summary for each set. It then introduces the clause that filters the groups themselves, and measures why that clause is separate from the clause that filters rows, in the same query.

The block below builds the schema and the sample data; every query in this lesson runs against the library.db file it creates.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL,
                    publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id));
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                  email TEXT, registered_at TEXT NOT NULL);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id),
                    member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL,
                    return_date TEXT);
INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'),
  (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir');
INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1),
  (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2),
  (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3),
  (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL);
INSERT INTO member VALUES (1,'Alice','Kane','[email protected]','2023-02-14'),
  (2,'Ben','Ortiz','[email protected]','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'),
  (4,'Derek','Voss','[email protected]','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'),
  (6,'Owen','Park','[email protected]','2025-01-18');
INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'),
  (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'),
  (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'),
  (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'),
  (12,4,4,'2025-06-20','2025-07-04');
SQL

Splitting Rows into Groups

The GROUP BY clause treats rows carrying the same value in the given columns as one group and produces exactly one result row for each group.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT author, COUNT(*) AS book_count
FROM book
GROUP BY author
ORDER BY book_count DESC, author;
SQL

author             book_count
-----------------  ----------
Oğuz Atay          2         
Jorge Luis Borges  1         
José Saramago      1         
Orhan Pamuk        1         
Yakup Kadri        1         
Yusuf Atılgan      1         

Seven rows split into six groups, and each group came back as a single row. Sorting is allowed on the column an aggregate function produces — this is valid because sorting is evaluated after grouping.

What can be written in the column list of a grouped query is restricted: either one of the grouping columns, or an aggregate function. Writing any other column is meaningless, because there is no single value for that column within the group. Enforcement of this rule varies by engine — some raise an error, some return an arbitrary row’s value.

A Null Value Forms Its Own Group

The behavior of null values under grouping differs from their behavior under a condition.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT branch_id, COUNT(*) AS book_count FROM book GROUP BY branch_id ORDER BY branch_id;
SQL

branch_id  book_count
---------  ----------
           1         
1          2         
2          2         
3          2         

The book with a null branch id was not discarded; it became a group of its own. Grouping treats null values as equal to each other, while the equality operator does not count them so. This looks inconsistent but is a necessary choice: otherwise every null value would form its own separate group and grouping would be useless.

The grouping key can be an expression too; the breakdown by decade shows this.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT (publication_year / 10) * 10 AS decade, COUNT(*) AS count
FROM book WHERE publication_year IS NOT NULL
GROUP BY decade
ORDER BY decade;
SQL

decade  count
------  -----
1930    1    
1970    3    
1980    1    
1990    1    

The Count Trap in an Outer Join

Grouping is most often used together with an outer join: the book count for every branch, including the branches with no books at all. In this combination, the spelling of the count directly determines the result.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT br.name AS branch, COUNT(b.book_id) AS book_count, COUNT(*) AS row_count
FROM branch AS br
LEFT JOIN book AS b ON b.branch_id = br.branch_id
GROUP BY br.branch_id, br.name
ORDER BY br.branch_id;
SQL

branch        book_count  row_count
------------  ----------  ---------
Central       2           2        
Bahcelievler  2           2        
Kadikoy       2           2        
Konak         0           1        

The two columns split apart on the last row. Konak has no books at all, but the outer join produced a row for it to preserve it, with the book columns left null. COUNT(*) counted that row and gave 1 — wrong. COUNT(b.book_id) skipped the null value and gave 0 — correct.

The rule: when counting after an outer join, the column counted should belong to the matching side, not the preserved side, and that column should be one that does not accept null values, preferably the primary key. This is the direct application of the previous lesson’s rule — aggregate functions skip null values.

A grouping key can be made up of more than one column; the group is then formed by the combination of those columns.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT br.city, br.name AS branch, COUNT(b.book_id) AS book_count
FROM branch AS br
LEFT JOIN book AS b ON b.branch_id = br.branch_id
GROUP BY br.city, br.name
ORDER BY br.city, br.name;
SQL

city      branch        book_count
--------  ------------  ----------
Ankara    Bahcelievler  2         
Ankara    Central       2         
Istanbul  Kadikoy       2         
Izmir     Konak         0         

Every combination of city and branch name became its own group. The two branches in Ankara were not merged into a single row, because the second key separates them.

Row Filtering versus Group Filtering

The WHERE clause filters rows; the HAVING clause filters groups. The difference between them comes from evaluation order: WHERE runs before grouping, HAVING runs after grouping. The order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

Asking the same question twice is enough to measure the result. First, without filtering: members who have borrowed at least twice.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT m.first_name, m.last_name, COUNT(*) AS loan_count
FROM loan AS l
JOIN member AS m ON m.member_id = l.member_id
GROUP BY m.member_id, m.first_name, m.last_name
HAVING COUNT(*) >= 2
ORDER BY loan_count DESC, m.member_id;
SQL

first_name  last_name  loan_count
----------  ---------  ----------
Alice       Kane       3         
Derek       Voss       3         
Ben         Ortiz      2         
Clara       Diaz       2         
Grace       Kim        2         

The condition HAVING COUNT(*) >= 2 ran over the groups; groups below two records were eliminated. Now add a row filter to the same query: count only returned loans.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT m.first_name, m.last_name, COUNT(*) AS loan_count
FROM loan AS l
JOIN member AS m ON m.member_id = l.member_id
WHERE l.return_date IS NOT NULL
GROUP BY m.member_id, m.first_name, m.last_name
HAVING COUNT(*) >= 2
ORDER BY loan_count DESC, m.member_id;
SQL

first_name  last_name  loan_count
----------  ---------  ----------
Alice       Kane       3         
Clara       Diaz       2         
Derek       Voss       2         

Five rows dropped to three, and the numbers changed. The WHERE clause eliminated the three unreturned records before grouping began; the groups were built from this reduced data, and the HAVING condition applied to the new counts. Derek dropped from three to two, Ben and Grace fell below the threshold and left the list.

The distinction can be summarized this way: WHERE answers “which rows will be counted,” HAVING answers “which groups will be shown.” The two cannot swap places. A condition that contains an aggregate function cannot be written inside WHERE, because no group exists yet at that stage.

The reverse also holds: a condition on an ungrouped column should not be written inside HAVING. The standard does not allow it, but enforcement varies by engine.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT m.first_name, COUNT(*) AS count
FROM loan AS l
JOIN member AS m ON m.member_id = l.member_id
GROUP BY m.member_id, m.first_name
HAVING l.return_date IS NOT NULL;
SQL

first_name  count
----------  -----
Alice       3    
Clara       2    
Derek       3    
Grace       2    

The query raised no error, but the result answers no real question: the counts match the unfiltered query, while the row set resembles the filtered one. A single row’s return date within the group was evaluated, and the fate of the whole group was tied to it. This kind of result is wrong without ever producing an error message; any condition that is not at the group level belongs inside WHERE.

HAVING referencing an aggregate function not present in the SELECT list is, by contrast, valid — group filtering is not limited to the columns being displayed.

Summary

• GROUP BY splits rows into sets by key value and produces exactly one row per set.
• A grouped query’s column list should hold only grouping columns and aggregate functions.
• Grouping treats null values as equal to each other; a null value forms its own group.
• After an outer join, a count should run over a non-null column on the matching side; COUNT(*) counts the preserved row too.
• WHERE filters rows before grouping, HAVING filters groups after grouping; the order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.
• Writing a condition that is not at the group level inside HAVING produces a wrong result without an error, in some engines.

Next Step

Grouping arranged the rows a single query produces. Some questions instead want to compare the results of two separate queries: what is common to both lists, what is in one but not the other, what the sum of the two is. The next lesson builds the operations that combine result sets as sets, and shows why keeping or discarding duplicate rows is an explicit choice.