Aggregate Functions

The previous three lessons stayed at row level: each result row corresponded to some combination of source rows. No matter how many tables a query joined, what it produced was still rows.

Questions like “how many books are there,” “what is the average loan duration,” and “what is the earliest publication year” ask for something else: a row set summarized down to a single value. The functions that do this are called aggregate functions. Their difference from scalar functions is that their input is not a single row but a set of rows.

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

Three Forms of Count

The count function has three spellings, and each counts something different.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT COUNT(*) AS rows, COUNT(publication_year) AS year_known,
       COUNT(DISTINCT author) AS distinct_authors
FROM book;
SQL

rows  year_known  distinct_authors
----  ----------  ----------------
7     6           6               

COUNT(*) counts rows; it never looks at column values, seven rows is seven. COUNT(publication_year) counts the non-null values in that column; the book with an unknown publication year did not enter the count, so the result came out six. COUNT(DISTINCT author) counts the non-null distinct values; seven books have six distinct authors.

This distinction is the first example of a general rule for aggregate functions: aggregate functions skip null values. The one exception is COUNT(*), because it looks at the row itself and not at any one column.

The same distinction answers a question directly in the loan table.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT COUNT(*) AS records, COUNT(return_date) AS returned FROM loan;
SQL

records  returned
-------  --------
12       9       

Nine of twelve loan records have been returned. The second column did not count the three records with a null return date — no condition was written, only the null-value rule running on its own.

Totals, Averages, and Extremes

Four more functions round out the same family.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT MIN(publication_year) AS earliest, MAX(publication_year) AS latest,
       SUM(publication_year) AS total, AVG(publication_year) AS average
FROM book;
SQL

earliest  latest  total  average         
--------  ------  -----  ----------------
1932      1995    11830  1971.66666666667

All four skipped the null value. The total is the sum of six numbers, and the average is that same total divided by six. What the average is divided by is the null value’s most commonly overlooked consequence.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT AVG(publication_year) AS average,
       SUM(publication_year) * 1.0 / COUNT(*)               AS divided_by_rows,
       SUM(publication_year) * 1.0 / COUNT(publication_year) AS divided_by_filled
FROM book;
SQL

average           divided_by_rows  divided_by_filled
----------------  ---------------  -----------------
1971.66666666667  1690.0           1971.66666666667 

The number in the middle is wrong. The total comes from six values, while COUNT(*) counts seven rows; the division behaves as if a nonexistent book had been counted with a publication year of zero. The average function and the third column give the same result, because both count only the filled values.

The decision rests on what the null value means. If it means “unknown,” excluding it from the average is correct. If it means “zero,” the schema is wrong: zero is a value, and a zero belongs in the column. Confusing the two silently corrupts numbers derived from the data.

Extremum functions work on text columns too, and the result depends on collation.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT MIN(title) AS first, MAX(title) AS last FROM book;
SQL

first      last 
---------  -----
Blindness  Yaban

The rule seen in the fourth lesson applies here too: which text counts as “smallest” is decided by the comparison ordering, and the default ordering varies by engine.

The Result on an Empty Set

What do aggregate functions return when they find no row at all? Count and sum answer this question differently.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT COUNT(*) AS rows, SUM(publication_year) AS total,
       COALESCE(SUM(publication_year), 0) AS safe
FROM book WHERE author = 'Unknown Author';
SQL

rows  total  safe
----  -----  ----
0            0   

The query found no rows at all. The count returned 0 — counting zero rows is a defined operation. The sum returned null, not 0: if there are no numbers at all, the total is undefined too. The distinction is logically consistent but causes trouble in practice, because code that feeds a total into a calculation usually expects a number.

There is also a point worth noting beyond this: a query containing an aggregate function with no grouping always returns exactly one row, even when it finds no source rows at all. The output above is not empty; it is one row whose contents are empty. This behavior changes once grouping is added, which is the next lesson’s subject.

The way to guard against this is turning the null value into a placeholder wherever the total is used — the third column’s spelling does exactly that.

Confusing Aggregation with a Column

An aggregate function reduces an entire row set to a single row. Asking for an ungrouped column in the same query creates a logical contradiction: whose value would be shown?

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title, MAX(publication_year) AS latest FROM book;
SQL

title      latest
---------  ------
Blindness  1995  

The query ran and produced a consistent-looking answer: the title of the most recent publication really is Blindness. This is nonetheless behavior that varies by engine. The standard does not allow an ungrouped column to be selected alongside an aggregate function; some engines raise an error, some return an arbitrary row’s value, and this engine picks the matching row when used together with an extremum function.

This spelling should be avoided as a rule. When the full row that produced the extreme value is what is wanted, the correct tool is sorting combined with a limit, or the window functions covered in the Advanced SQL course.

Aggregation Combined with a Join

Aggregate functions also work over a joined result. Because loan duration is the difference between two date columns, it is summed over a computed expression.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT COUNT(*) AS records,
       COUNT(return_date) AS returned,
       ROUND(AVG(julianday(return_date) - julianday(pickup_date)), 2) AS average_days
FROM loan;
SQL

records  returned  average_days
-------  --------  ------------
12       9         16.11       

Three columns summarize the same table at three different scales: twelve records exist, nine of them are returned, and the average duration of the returned ones is a little over sixteen days. The average’s denominator is again nine; for records that have not been returned, the difference cannot be computed, so a null value is produced and skipped.

The row-multiplying effect of a join can turn into a trap here. Joining a book with its loan records and counting produces a count in which a book borrowed more than once is counted more than once. In such a query, the spelling COUNT(DISTINCT book_id) gives the correct result.

Summary

• Aggregate functions reduce a row set to a single value, and when written without grouping, the result is always exactly one row.
• COUNT(*) counts rows, COUNT(column) counts the non-null values in that column, and a distinct count counts duplicates once.
• Aggregate functions skip null values; the average’s denominator is the count of filled values, not the row count.
• Count returns zero on an empty set; sum returns a null value.
• Selecting an ungrouped column together with an aggregate function is not standard, and its behavior varies by engine.
• When a join multiplies rows, counts inflate; a distinct count corrects it.

Next Step

Every aggregation so far reduced the whole table to a single result. What is actually useful in practice is usually not “how many in total” but “how many for each branch,” “how many for each member.” The next lesson builds grouping, which splits rows into sets and produces a separate summary for each set, and measures within the same query why the clause that filters groups is separate from the WHERE clause.