Lesson 7
Groups: GROUP BY and HAVING
For readers who finished lesson 6 and can count and add up with aggregate functions.
- One row per group
- Several aggregates per group
- Columns beside an aggregate
- HAVING filters groups
- WHERE or HAVING
- The order the clauses run
- 5 exercises
Every query runs on real PostgreSQL, in your browser. It downloads once, about 6 MB, then starts in a few seconds.
For a reference page on the same statements, read SQL HAVING Clause: GROUP BY Filters, Examples, and HAVING vs WHERE.
The idea
GROUP BY makes one row per group
GROUP BY gathers the rows that share a value into a group. An aggregate in the select list is then worked out for each group, and the answer has one row per group.
Try it
Count the products of each category
select category, count(*) from products group by category;
6 rows, one per category. Accessories has the most products: 9.
Try it
Every aggregate works per group
select status, count(*), min(order_date), max(order_date) from orders group by status;
One row per status. The 84 pending orders are the most recent ones: from 2026-09-04 on.
Watch out
Each column is grouped or inside an aggregate
select category, name, count(*)
from products
group by category;
ERROR: column "products.name" must appear in the GROUP BY clause or be used in an aggregate function
A category has several products, so a group has several names and PostgreSQL will not pick one. Put name in GROUP BY, which makes a group per product, or inside an aggregate such as min(name).
Try it
HAVING keeps the groups that pass a test
HAVING is to groups what WHERE is to rows: its condition can use an aggregate, and the groups that fail it are left out.
select customer_id, count(*) from orders group by customer_id having count(*) >= 5;
80 customers have placed 5 orders or more.
Watch out
An aggregate cannot go in WHERE
select customer_id, count(*)
from orders
where count(*) >= 5
group by customer_id;
ERROR: aggregate functions are not allowed in WHERE
WHERE tests one row at a time, before any group exists, so there is nothing to count yet. A condition on a single row goes in WHERE; a condition on a count, a sum or an average goes in HAVING.
The idea
The clauses run in a fixed order
That order explains the last two slides. It also explains why a name given with AS works in ORDER BY, which runs after SELECT, while where gross > 100 fails with ERROR: column "gross" does not exist.
Try it
Sort the groups by their aggregate
select category, round(avg(price), 2) as avg_price from products group by category order by avg_price desc;
Computers average 1199.00, Accessories 90.31. ORDER BY uses the name avg_price, given in SELECT.
Exercise 1 of 5
Show each product category with its number of products.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select category, count(*) from products group by category
Exercise 2 of 5
Show each order status with its number of orders.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select status, count(*) from orders group by status
Exercise 3 of 5
Row order countsShow each country with its number of customers, the most customers first. For the same number, sort by country name.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select country, count(*) from customers group by country order by count(*) desc, country
Exercise 4 of 5
Show the id and the order count of every customer with at least 5 orders.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select customer_id, count(*) from orders group by customer_id having count(*) >= 5
Exercise 5 of 5
Show each category whose average price is over 200, with that average rounded to 2 decimals.
Expected result
It shows once PostgreSQL is ready.
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.
Show answer
select category, round(avg(price), 2) from products group by category having avg(price) > 200