انتقل إلى المحتوى
أمين أبوزيد
Englishالعربية
منشورBEG-050-L05

دوال التجميع وGROUP BY وHAVING

  • الدرس 5 من 9
  • 10 دقيقة قراءة
  • مبتدئ
في هذا الدرس 11 موضوعات

هذا الدرس متاح حاليًا باللغة الإنجليزية فقط، ولم تُنشر نسخته العربية بعد.

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.

FunctionReturns
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

WHEREHAVING
Filtersindividual rowsgroups
Runsbefore groupingafter grouping and aggregating
Can use aggregates?NoYes

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.

أظهر الحل A1

A1. SELECT COUNT(*) FROM members;

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

أظهر الحل 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(*)?

أظهر الحل 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.

أظهر الحل 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.

أظهر الحل 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;

أظهر الحل 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.

أظهر الحل 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?

أظهر الحل 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:

  1. How many loans are there in total, and how many are still open?
  2. How many books does each author have, showing only authors with exactly one book?
  3. 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

  1. What does COUNT(*) return for a table with five rows, even if some columns in some rows are NULL?
  2. In which clause does COUNT(*) > 3 belong?
  3. Why does SELECT author_id, title, COUNT(*) ... GROUP BY author_id fail?
  4. What does SUM return when no rows match?
  5. A query has WHERE and HAVING. Which one runs first?
أظهر الإجابات
  1. 5: COUNT(*) counts rows, regardless of NULL values in other columns.
  2. HAVING.
  3. title is neither grouped nor aggregated, so there is no single title per group.
  4. NULL.
  5. 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.

التطبيق

طبّق هذا الدرس بمختبر الوحدة وتمارينها واختبارها.

المختبر والتمارين الاختبار

تعليم الدرس كمكتمل اختياري ويمكنك التراجع عنه في أي وقت. يبقى تقدمك محفوظًا في هذا المتصفح.

تابع إلى الدرس التالي

اكتب للبحث في المستويات والمراحل والوحدات والدروس.