--- title: 'Aggregate Functions' source: 'https://academia.sh/en/courses/sql-fundamentals/aggregate-functions' course: 'SQL Fundamentals' language: en updated: '2026-08-23T07:00:52+00:00' license: 'CC BY-SA 4.0' --- # Aggregate Functions Reducing a row set to a single value with count, sum, average, and extremum functions; how null values are skipped, the difference between a starred count and a column count, and the result on an empty set. 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. ```sh 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','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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? ```sh 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. ```sh 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.