Skip to content
SQL

SQL GROUP BY and HAVING

Ihsan Ullah · 11 min read
SQL GROUP BY cover for Lesson 10 of the free SQL course: GROUP BY and HAVING

SQL course · Module 3: Aggregation · Lesson 10 of 40

Quick answer: SQL GROUP BY splits the rows into groups that share the same value and runs the aggregate functions once per group: SELECT status, count() FROM orders GROUP BY status; returns one row per status. Every other column in the SELECT list must be in GROUP BY or inside an aggregate. HAVING filters the groups after they are formed (HAVING count() > 1), while WHERE filters the rows before grouping.

In this lesson you will learn:

  • how SQL GROUP BY works with one and with several columns
  • what the “must appear in the GROUP BY clause” error means and how to fix it
  • how HAVING differs from WHERE, and when to use each
  • how to group by an expression, such as the month of a date
  • how NULL values are grouped and how to sort by an aggregate

Before you start: Lesson 9, SQL Aggregate Functions: COUNT, SUM, AVG, MIN and MAX.

We ran every example on PostgreSQL 16.15 with the shop database from Lesson 3. GROUP BY and HAVING are described the same way in the current PostgreSQL 18 manual.

How SQL GROUP BY works

In Lesson 9, aggregates turned the whole table into one row:

SELECT count(*) AS orders FROM orders;
 orders 
--------
     22
(1 row)

To get one count per status, add GROUP BY status:

SELECT status, count(*) AS orders
FROM orders
GROUP BY status
ORDER BY status;
  status   | orders 
-----------+--------
 cancelled |      2
 delivered |     16
 pending   |      1
 shipped   |      3
(4 rows)

PostgreSQL puts the 22 orders into four piles, one per status, and runs count(*) once per pile. In the manual’s words, each group becomes “one group row that represents all rows in the group” (PostgreSQL docs: The GROUP BY and HAVING Clauses). The four counts add up to 22.

GROUP BY does not promise any order; without ORDER BY, rows come back “in whatever order the system finds fastest to produce” (PostgreSQL docs: SELECT).

Each group can have several aggregates. Here are price statistics for each product category:

SELECT category, count(*) AS products, min(price) AS cheapest, max(price) AS dearest,
       round(avg(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY category;
  category   | products | cheapest | dearest | avg_price 
-------------+----------+----------+---------+-----------
 Accessories |        1 |    45.00 |   45.00 |     45.00
 Electronics |        5 |    24.99 |  149.00 |     71.48
 Home Office |        2 |    32.00 |  259.00 |    145.50
 Stationery  |        2 |     6.75 |   12.50 |      9.63
(4 rows)

Revenue per order

Grouping order_items by order_id turns 40 order lines into one total per order. This is the sum(quantity * unit_price) from Lesson 9, now once per group:

SELECT order_id, count(*) AS lines, sum(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
ORDER BY order_total DESC, order_id
LIMIT 5;
 order_id | lines | order_total 
----------+-------+-------------
       11 |     2 |      181.00
       22 |     2 |      174.00
       14 |     3 |      158.99
        3 |     1 |      149.00
       16 |     1 |      149.00
(5 rows)

Orders 3 and 16 tie at 149.00, so order_id is the tie-breaker, as in Lesson 7. Revenue per customer or per month needs both orders and order_items, so it waits for joins in Lesson 12.

GROUP BY several columns

With two columns, each group is one combination of values. Here are the orders of customers 1 to 3, split by status:

SELECT customer_id, status, count(*) AS orders
FROM orders
WHERE customer_id <= 3
GROUP BY customer_id, status
ORDER BY customer_id, status;
 customer_id |  status   | orders 
-------------+-----------+--------
           1 | cancelled |      1
           1 | delivered |      3
           2 | delivered |      2
           2 | shipped   |      1
           3 | delivered |      2
           3 | pending   |      1
(6 rows)

Customer 1 has two groups: one cancelled order and three delivered ones. A combination that does not exist (customer 1 with shipped) does not appear.

The “must appear in the GROUP BY clause” error

This is the most common GROUP BY error. Here we group by customer but also ask for status:

SELECT customer_id, status, count(*) AS orders FROM orders GROUP BY customer_id;
ERROR:  column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT customer_id, status, count(*) AS orders FROM orders G...
                            ^

Customer 1 is now one row with two different statuses, and PostgreSQL cannot know which one to show. The rule from the manual: “In general, if a table is grouped, columns that are not listed in GROUP BY cannot be referenced except in aggregate expressions.” The one exception: if you group by a table’s primary key, PostgreSQL also lets you select that table’s other columns, because each key value belongs to exactly one row.

There are two fixes. Add the column to GROUP BY (as in the previous section), or put it inside an aggregate that picks one value per group:

SELECT customer_id, count(*) AS orders, max(order_date) AS last_order
FROM orders
WHERE customer_id <= 3
GROUP BY customer_id
ORDER BY customer_id;
 customer_id | orders | last_order 
-------------+--------+------------
           1 |      4 | 2026-09-29
           2 |      3 | 2026-10-14
           3 |      3 | 2026-10-30
(3 rows)

max(order_date) gives one date per customer: the date of the last order.

MySQL also rejects this query (error 1055), because its ONLY_FULL_GROUP_BY mode is on by default (MySQL 9.7 manual: MySQL Handling of GROUP BY). SQLite gave no error in our 3.53.1 test: it takes status from one of the rows in the group, and “you usually do not know which input row is used” (SQLite: SELECT). Do not rely on that; write the query the strict way.

HAVING: filter the groups

Which countries have more than one customer? You cannot use WHERE for this, because the count does not exist until the rows are grouped. HAVING filters groups:

SELECT country, count(*) AS customers
FROM customers
GROUP BY country
HAVING count(*) > 1;
 country  | customers 
----------+-----------
 Pakistan |         4
(1 row)

The six countries with one customer were removed. These are the orders worth more than 150:

SELECT order_id, sum(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
HAVING sum(quantity * unit_price) > 150
ORDER BY order_total DESC;
 order_id | order_total 
----------+-------------
       11 |      181.00
       22 |      174.00
       14 |      158.99
(3 rows)

HAVING repeats the full sum(...) because PostgreSQL does not accept the alias order_total there (Lesson 11 explains why).

WHERE vs HAVING

“WHERE filters individual rows before the application of GROUP BY, while HAVING filters group rows created by GROUP BY” (PostgreSQL docs: SELECT). Using both changes the answer. First, customers with at least two orders:

SELECT customer_id, count(*) AS orders
FROM orders
GROUP BY customer_id
HAVING count(*) >= 2
ORDER BY customer_id;
 customer_id | orders 
-------------+--------
           1 |      4
           2 |      3
           3 |      3
           4 |      2
           5 |      2
           6 |      2
           7 |      2
           8 |      2
           9 |      2
(9 rows)

Now remove cancelled orders first with WHERE:

SELECT customer_id, count(*) AS orders
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
HAVING count(*) >= 2
ORDER BY customer_id;
 customer_id | orders 
-------------+--------
           1 |      3
           2 |      3
           3 |      3
           4 |      2
           5 |      2
           6 |      2
           7 |      2
           8 |      2
(8 rows)

Customer 1 drops from 4 to 3, and customer 9 (Chloe) disappears: one of her two orders was cancelled, so HAVING removes her group. A simple rule: if a condition uses an aggregate, put it in HAVING; otherwise put it in WHERE. The PostgreSQL tutorial adds that a condition in WHERE is also faster, “because we avoid doing the grouping and aggregate calculations for all rows that fail the WHERE check” (PostgreSQL tutorial: Aggregate Functions).

Group by an expression: orders per month

You can also group by a calculated value. With date_trunc from Lesson 8, every date in a month becomes the first day of that month:

SELECT date_trunc('month', order_date)::date AS month, count(*) AS orders
FROM orders
GROUP BY date_trunc('month', order_date)::date
ORDER BY month;
   month    | orders 
------------+--------
 2026-06-01 |      4
 2026-07-01 |      5
 2026-08-01 |      4
 2026-09-01 |      4
 2026-10-01 |      5
(5 rows)

The dates are fixed in the table, so this result does not change from day to day. The ::date cast keeps time zones out of the result (Lesson 8).

A CASE expression works the same way. Here PostgreSQL also lets us write the alias price_band in GROUP BY, an extension to standard SQL:

SELECT CASE
           WHEN price < 20  THEN 'budget'
           WHEN price < 100 THEN 'standard'
           ELSE 'premium'
       END AS price_band,
       count(*) AS products
FROM products
GROUP BY price_band
ORDER BY products DESC, price_band;
 price_band | products 
------------+----------
 standard   |        6
 budget     |        2
 premium    |        2
(3 rows)

PostgreSQL, MySQL (MySQL 9.7 manual: Problems with Column Aliases) and SQLite (in our test) accept an alias in GROUP BY, but SQL Server does not: “A GROUP BY column expression can’t contain a column alias that you define in the SELECT list” (Microsoft Learn: GROUP BY). Repeating the expression, as in the month query, works everywhere.

NULL forms its own group

Hira Siddiqui has no city. When we group by city, her NULL is not dropped:

SELECT city, count(*) AS customers
FROM customers
GROUP BY city
ORDER BY city;
  city   | customers 
---------+-----------
 Berlin  |         1
 Chicago |         1
 Karachi |         1
 Lahore  |         2
 London  |         1
 Milan   |         1
 Paris   |         1
 Toronto |         1
 (null)  |         1
(9 rows)

(We used \pset null '(null)' from Lesson 6.) All NULLs go into one group, even though NULL = NULL is not true in a WHERE. Other databases do the same. The MySQL manual says “Two NULL values are regarded as equal in a GROUP BY” (MySQL 9.7 manual: Working with NULL Values), and the SQLite and SQL Server pages linked above state the same rule. Lahore has 2, because customers 1 and 10 live there.

Sort by an aggregate

ORDER BY can use an aggregate, so the biggest groups come first:

SELECT country, count(*) AS customers
FROM customers
GROUP BY country
ORDER BY count(*) DESC, country;
 country  | customers 
----------+-----------
 Pakistan |         4
 Canada   |         1
 France   |         1
 Germany  |         1
 Italy    |         1
 UK       |         1
 USA      |         1
(7 rows)

ORDER BY customers DESC (the alias) works too. country sorts the six ties.

Try it yourself

Exercise 1: Categories with at least two products

For each category, show the number of products and the average price rounded to 2 decimals. Keep only categories with at least 2 products, most expensive average first.

Exercise 2: Big orders

Show the orders that have more than 2 lines in order_items, with the number of lines and the total units.

Exercise 3: Busy months

Count the orders per month without cancelled orders. Show only months with at least 4 such orders.

Solutions

Solution 1

SELECT category, count(*) AS products, round(avg(price), 2) AS avg_price
FROM products
GROUP BY category
HAVING count(*) >= 2
ORDER BY avg_price DESC;
  category   | products | avg_price 
-------------+----------+-----------
 Home Office |        2 |    145.50
 Electronics |        5 |     71.48
 Stationery  |        2 |      9.63
(3 rows)

Accessories has one product, so HAVING removes it.

Solution 2

SELECT order_id, count(*) AS lines, sum(quantity) AS units
FROM order_items
GROUP BY order_id
HAVING count(*) > 2
ORDER BY order_id;
 order_id | lines | units 
----------+-------+-------
        7 |     3 |     4
       14 |     3 |     3
(2 rows)

count(*) counts the lines in each order group.

Solution 3

SELECT date_trunc('month', order_date)::date AS month, count(*) AS orders
FROM orders
WHERE status <> 'cancelled'
GROUP BY date_trunc('month', order_date)::date
HAVING count(*) >= 4
ORDER BY month;
   month    | orders 
------------+--------
 2026-07-01 |      5
 2026-09-01 |      4
 2026-10-01 |      5
(3 rows)

June and August each had 4 orders, but one was cancelled, so WHERE leaves 3 and HAVING drops them.

Common mistakes

  • A plain column that is not grouped. You get ERROR: column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function. Add the column to GROUP BY, or wrap it in an aggregate such as max().
  • An aggregate in WHERE. SELECT customer_id, count() FROM orders WHERE count() >= 2 GROUP BY customer_id; fails with ERROR: aggregate functions are not allowed in WHERE. Move the condition to HAVING.
  • HAVING before GROUP BY. SELECT country, count() FROM customers HAVING count() > 1 GROUP BY country; fails with ERROR: syntax error at or near "GROUP". The written order is WHERE, GROUP BY, HAVING, ORDER BY.
  • Expecting GROUP BY to sort. It does not promise any order. Always add ORDER BY.

Frequently asked questions

Can you use GROUP BY without an aggregate function?

Yes. SELECT status FROM orders GROUP BY status returns the same four rows as SELECT DISTINCT status FROM orders; the manual says grouping without aggregates “effectively calculates the set of distinct values in a column”. DISTINCT is clearer for this.

What is the difference between WHERE and HAVING in SQL?

WHERE filters rows before they are grouped and cannot contain aggregates. HAVING filters the groups after GROUP BY and usually contains an aggregate such as count(*) > 1. A query can use both.

Can I use HAVING without GROUP BY?

Yes. HAVING “turns a query into a grouped query even if there is no GROUP BY clause” (PostgreSQL manual), so the whole table is one group and HAVING decides whether its one summary row is returned. This is rarely useful.

Summary

  • SQL GROUP BY makes one row per group of equal values, and aggregates are computed once per group.
  • Every plain column in SELECT must be in GROUP BY or inside an aggregate (SQLite does not check this, but its answer is unpredictable).
  • WHERE filters rows before grouping; HAVING filters groups after it, using aggregates.
  • You can group by expressions such as date_trunc('month', order_date)::date or a CASE; NULLs form one group.
  • GROUP BY does not sort: use ORDER BY, also with aggregates such as count(*) DESC.

Grouped counts and totals per month or category are the core of most reports in the data analyst roadmap and the data engineer roadmap.

Next lesson

Next: Lesson 11, SQL Order of Execution: Why Aliases and Aggregates Fail in WHERE.

Sources

Ihsan Ullah

Written & tested by Ihsan Ullah

Data analyst with 3 years of experience and a Bachelor's degree in Computer Science. I write clear, practical guides on data, SQL, Python and AI.

More about me →

Leave a comment

Your email address will not be published. Required fields are marked *