PublishedBEG-050-L05

Aggregates, GROUP BY, HAVING

Lesson 5 of 9
10 min read
Beginner

On This Lesson 11 topics

Lesson Overview
Learning Objectives
Written Explanation
Examples
Backend Insight
Common Mistakes
Exercises
Mini Challenge
Summary & Next Lesson
References
Practice

Lesson Overview

So far each query returned individual rows. Now you will ask questions about many rows at once: "How many books are there?", "How many books does each author have?", "Which members have borrowed at least two books?" These are summaries, and SQL has three tools for them: aggregate functions (COUNT, SUM, AVG, MIN, MAX), GROUP BY (one result row per group), and HAVING (a filter on groups).

In the library picture, the librarian no longer fetches individual cards. She counts them, drawer by drawer, and reports totals.

The lesson ends on the module's most common beginner error: putting a condition on an aggregate in WHERE instead of HAVING. You will see the real error message, and learn why WHERE cannot do that job.

This lesson builds on Lesson 4 (SELECT, WHERE, ORDER BY, NULL).

Learning Objectives

By the end of this lesson you will be able to:

Use COUNT, SUM, AVG, MIN, and MAX and say what each returns.
Explain how aggregates treat NULL, and the difference between COUNT(*) and COUNT(column).
Use GROUP BY to produce one row per group.
Say which columns may appear in a grouped SELECT.
Explain the difference between WHERE and HAVING, and use each correctly in one query.
Predict the result of a grouped query on a small table.

Written Explanation

Aggregate functions

An aggregate function takes many rows and returns one value.

Function	Returns
`COUNT(*)`	Number of rows
`COUNT(column)`	Number of rows where the column is not `NULL`
`SUM(column)`	Total of the values
`AVG(column)`	Average of the values
`MIN(column)`, `MAX(column)`	Smallest and largest value

sql

SELECT COUNT(*) AS books, MIN(published_year) AS oldest, MAX(published_year) AS newest
FROM books;

One row comes back, whatever the size of the table.

NULL handling. The PostgreSQL manual says count(*) counts input rows, while count of an expression counts rows where it is not null. Most other aggregates (sum, avg, min, max) skip null inputs. Another rule from the manual: except for count, these functions return NULL when no rows were selected. In particular, SUM of no rows is NULL, not zero. You will see both facts in the examples.

`GROUP BY`

GROUP BY splits the rows into groups that share the same value in the listed column, and the query returns one row per group:

sql

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

Read it as: "put the books in one pile per author_id, then count each pile."

The rule for the SELECT list: in a grouped query, each selected column must either appear in GROUP BY or be inside an aggregate function. Once rows are grouped, a column such as title has several values per group, and the database cannot know which one you want. It refuses with an error (Example 3). NULL forms a group of its own: authors with no country are counted together.

Row order: GROUP BY does not promise any sort order. Add ORDER BY when you want one.

`WHERE` versus `HAVING`

	`WHERE`	`HAVING`
Filters	individual rows	groups
Runs	before grouping	after grouping and aggregating
Can use aggregates?	No	Yes

The PostgreSQL manual describes it exactly like this: WHERE filters rows before grouping, HAVING filters groups after grouping and aggregation. So COUNT(*) >= 2, a statement about a group, can only live in HAVING: when WHERE runs, no groups exist yet. PostgreSQL stops you with an error.

The clause order is:

sql

SELECT   ...
FROM     ...
WHERE    ...      -- filter rows
GROUP BY ...      -- make groups
HAVING   ...      -- filter groups
ORDER BY ...      -- sort the result
LIMIT    ...;

A good test for where a condition belongs: is it about one row, or about a whole group? "Published after 1960" is about one row (WHERE). "Has at least two books" is about a group (HAVING). A condition on a grouped column itself, such as author_id > 3, would work in either clause, but WHERE is better because it discards rows before the grouping work is done.

Result names

Aggregates have no natural column name, so give them one with AS: COUNT(*) AS books. You can then sort by the alias, as in ORDER BY books DESC.

Examples

All results are real output from PostgreSQL 18.3 on the seeded library (5 authors, 11 books, 5 members, 8 loans).

Example 1: summarizing the whole table (simple)

sql

SELECT COUNT(*) AS books,
       COUNT(published_year) AS with_year,
       MIN(published_year) AS oldest,
       MAX(published_year) AS newest,
       AVG(published_year) AS avg_year
FROM books;

text

 books | with_year | oldest | newest |       avg_year        
-------+-----------+--------+--------+-----------------------
    11 |        10 |   1956 |   2008 | 1977.4000000000000000
(1 row)

There are 11 books, but only 10 have a year: one has NULL, so COUNT(published_year) gives 10, and AVG is computed over those 10. (The long decimal is PostgreSQL's display of an exact average; ROUND(AVG(published_year), 1) gives 1977.4.)

And the empty-set rule:

sql

SELECT COUNT(*) FROM books WHERE published_year > 3000;
SELECT SUM(published_year) FROM books WHERE published_year > 3000;

text

 count 
-------
     0
 sum 
-----

COUNT gives 0, but SUM over no rows is NULL, shown as blank.

Example 2: books per author, then `HAVING` (practical)

sql

SELECT author_id, COUNT(*) AS books
FROM books
GROUP BY author_id
ORDER BY author_id;

text

 author_id | books 
-----------+-------
         1 |     4
         2 |     2
         3 |     1
         4 |     3
         5 |     1
(5 rows)

Keep only authors with at least two books, biggest first:

sql

SELECT author_id, COUNT(*) AS books
FROM books
GROUP BY author_id
HAVING COUNT(*) >= 2
ORDER BY books DESC, author_id;

text

 author_id | books 
-----------+-------
         1 |     4
         4 |     3
         2 |     2
(3 rows)

Now combine WHERE and HAVING: only books from 1960 onward are counted (rows filtered first), and then only authors with at least two of those are kept (groups filtered after):

sql

SELECT author_id, COUNT(*) AS books, MIN(published_year) AS first_pub
FROM books
WHERE published_year >= 1960
GROUP BY author_id
HAVING COUNT(*) >= 2
ORDER BY author_id;

text

 author_id | books | first_pub 
-----------+-------+-----------
         1 |     4 |      1968
         4 |     3 |      1979
(2 rows)

Author 2 dropped out: only one of their two books is from 1960 or later (Arrow of God, 1964), so the group has just one row. The WHERE changed what got counted, which is exactly why order matters.

Example 3: the two classic errors (realistic)

The planned mistake: filtering on COUNT(*) in WHERE.

sql

SELECT author_id, COUNT(*) FROM books WHERE COUNT(*) >= 2 GROUP BY author_id;

text

ERROR:  aggregate functions are not allowed in WHERE

The message tells you the fix: move the aggregate condition to HAVING. And the grouping rule:

sql

SELECT author_id, title, COUNT(*) FROM books GROUP BY author_id;

text

ERROR:  column "books.title" must appear in the GROUP BY clause or be used in an aggregate function

Author 1 has four titles, so there is no single title for that group. Fix it by removing title, or by grouping by it too (which would give one row per author-and-title pair and probably not what you wanted). A real report: open loans per member.

sql

SELECT member_id, COUNT(*) AS open_loans
FROM loans
WHERE returned_on IS NULL
GROUP BY member_id
ORDER BY member_id;

The result has four rows, one each for members 1 to 4, each with one open loan. Member 5 has no loans at all, so does not appear: groups only exist for rows that exist. Lesson 6 shows how to include members like that.

Backend Insight

Aggregates are why you ask the database for summaries instead of loading every row into Java and counting there. A dashboard showing "orders per day" or "revenue per customer" should send one GROUP BY query and receive a handful of rows, rather than pulling a million rows over the network. The database is built for this work, and often can use its indexes to do it faster.

Two production habits follow from this lesson. First, always consider NULL: COUNT(column) quietly differs from COUNT(*), and SUM over nothing is NULL, which may arrive in your Java code as a missing value rather than 0. The manual suggests COALESCE to supply a default, which you can look up when you need it. Second, put row-level filters in WHERE: it reduces the data before grouping, and usually makes the query cheaper than filtering afterwards in HAVING.

Common Mistakes

Mistake 1: An aggregate in `WHERE`

Incorrect: ... WHERE COUNT(*) > 3 GROUP BY author_id
Correct: ... GROUP BY author_id HAVING COUNT(*) > 3

Mistake 2: Selecting an ungrouped column

Incorrect: SELECT author_id, title, COUNT(*) ... GROUP BY author_id
Correct: select only grouped columns and aggregates.

Mistake 3: Forgetting that `COUNT(column)` skips `NULL`

If you want the number of rows, use COUNT(*). If you want the number of rows that have a value, use COUNT(column), and know that this is what you chose.

Mistake 4: Putting a row filter in `HAVING`

HAVING author_id > 3 works but filters late, after grouping. Put conditions about single rows in WHERE.

Mistake 5: Expecting groups to appear sorted

Without ORDER BY, the order of the group rows is unspecified. In our run, the group for author 5 came before author 4 in one query.

Exercises

Try each exercise yourself before opening the solutions.

Level A: Foundation

A1. Write a query returning the number of members.

Show solution A1

A1. SELECT COUNT(*) FROM members;

A2. Write a query returning the earliest loaned_on date and the latest one.

Show solution A2

A2. SELECT MIN(loaned_on), MAX(loaned_on) FROM loans;

A3. What does COUNT(returned_on) count in loans? How does it differ from COUNT(*)?

Show solution A3

A3. It counts the loans that have a return date (non-NULL), so the returned ones. COUNT(*) counts all loans, returned or not.

Level B: Application

B1. Write a query showing, per member_id, how many loans they have, most loans first.

Show solution B1

B1. SELECT member_id, COUNT(*) AS loans FROM loans GROUP BY member_id ORDER BY loans DESC;

B2. Write a query that lists author_id only for authors who have at least three books.

Show solution B2

B2. SELECT author_id FROM books GROUP BY author_id HAVING COUNT(*) >= 3; (You can select author_id without selecting the count.) On the seeded data this returns authors 1 and 4.

B3. Which of these is wrong, and why? (a) SELECT member_id, COUNT(*) FROM loans GROUP BY member_id; (b) SELECT member_id, due_on, COUNT(*) FROM loans GROUP BY member_id;

Show solution B3

B3. (b) is wrong: due_on is neither grouped nor aggregated, so PostgreSQL rejects it. (a) is fine.

Level C: Challenge

C1. Write the query for "members with two or more loans loaned on or after 2025-10-01", showing the number of such loans. Say which condition goes in WHERE and which in HAVING, and why.

Show solution C1

C1. The date condition is about single rows, so it goes in WHERE; "two or more loans" is about a group, so it goes in HAVING:

sql

SELECT member_id, COUNT(*) AS loans
FROM loans
WHERE loaned_on >= '2025-10-01'
GROUP BY member_id
HAVING COUNT(*) >= 2
ORDER BY member_id;

On the seeded data only member 3 qualifies (two loans since 1 October). Member 1 has three loans in total, but only one of them is that recent, so the WHERE leaves them with a group of one.

C2. A teammate wrote SELECT AVG(published_year) FROM books WHERE author_id = 99; and their Java code crashed on a missing value. What does the query return, and why?

Show solution C2

C2. No author has id 99, so no rows are selected. AVG over zero rows returns NULL (not 0), and Java code that expects a number must handle that. The same applies to SUM, MIN, and MAX; only COUNT returns 0.

Mini Challenge

Answer these questions with queries, and write the result of each:

How many loans are there in total, and how many are still open?
How many books does each author have, showing only authors with exactly one book?
For each member with at least two loans, what is the earliest loaned_on?

Check your work: question 1 can be done with one query using COUNT(*) and COUNT(returned_on), or with two. Question 2 uses HAVING COUNT(*) = 1, not WHERE. Question 3 shows member_id, MIN(loaned_on), and HAVING COUNT(*) >= 2.

Summary & Next Lesson

Key takeaways

Aggregates (COUNT, SUM, AVG, MIN, MAX) turn many rows into one value, and most skip NULL.
COUNT(*) counts rows; COUNT(column) counts non-NULL values; SUM over no rows is NULL.
GROUP BY gives one row per group; selected columns must be grouped or aggregated.
WHERE filters rows before grouping; HAVING filters groups after, and is the only place for conditions on aggregates.
Add ORDER BY if you want a defined result order.

Next lesson

So far each query has used one table. The next lesson, "Joins", combines related tables, using the foreign keys you defined, so you can show author names with their books and members with their loans.

Self-check

What does COUNT(*) return for a table with five rows, even if some columns in some rows are NULL?
In which clause does COUNT(*) > 3 belong?
Why does SELECT author_id, title, COUNT(*) ... GROUP BY author_id fail?
What does SUM return when no rows match?
A query has WHERE and HAVING. Which one runs first?

Show answers

5: COUNT(*) counts rows, regardless of NULL values in other columns.
HAVING.
title is neither grouped nor aggregated, so there is no single title per group.
NULL.
WHERE: it filters rows before they are grouped.

References

PostgreSQL documentation, Aggregate Functions. Supports count(*) versus count(expression), most aggregates ignoring null inputs, and that apart from count they return NULL on no rows (including sum).
PostgreSQL documentation, Table Expressions: GROUP BY and HAVING. Supports that ungrouped columns may only appear inside aggregates, and that WHERE filters rows before grouping while HAVING filters groups after.
Result tables and error messages came from real runs on PostgreSQL 18.3. The advice that WHERE is cheaper than HAVING for row conditions and the point about computing summaries in the database are general practice that I did not verify against a cited page.

Practice

Apply this lesson with the module's lab, exercises and quiz.

Lab and exercises Quiz

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.

Continue to next lesson

Lesson Overview

Learning Objectives

Written Explanation

Aggregate functions

GROUP BY

WHERE versus HAVING

Result names

Examples

Example 1: summarizing the whole table (simple)

Example 2: books per author, then HAVING (practical)

Example 3: the two classic errors (realistic)

Backend Insight

Common Mistakes

Mistake 1: An aggregate in WHERE

Mistake 2: Selecting an ungrouped column

Mistake 3: Forgetting that COUNT(column) skips NULL

Mistake 4: Putting a row filter in HAVING

Mistake 5: Expecting groups to appear sorted

Exercises

Level A: Foundation

Level B: Application

Level C: Challenge

Mini Challenge

Summary & Next Lesson

Key takeaways

Next lesson

Self-check

References

Practice

`GROUP BY`

`WHERE` versus `HAVING`

Example 2: books per author, then `HAVING` (practical)

Mistake 1: An aggregate in `WHERE`

Mistake 3: Forgetting that `COUNT(column)` skips `NULL`

Mistake 4: Putting a row filter in `HAVING`