SQLite HAVING Clause: Filter Groups After GROUP BY (Tested Examples)

Ninad Pathak

October 7, 2026

SQLite rejects a filter like WHERE SUM(order_value) > 250 with misuse of aggregate: SUM(), because WHERE runs before the groups exist. HAVING runs after, so it can see their totals. In this article I’ll walk you through filtering grouped results with HAVING, including where WHERE still belongs.

TLDR

HAVING filters groups after GROUP BY. WHERE filters rows before grouping.
Put aggregate conditions in HAVING: HAVING SUM(order_value) > 250.
SQLite lets HAVING use an alias from the SELECT list: HAVING total > 250.

Combine conditions with AND and OR, and use aggregates that aren’t in the SELECT list.
HAVING on a query with no aggregates is an error in SQLite.

What is the HAVING clause in SQLite?

HAVING is a condition that SQLite checks once per group. SQLite’s docs: “If a HAVING clause is specified, it is evaluated once for each group of rows as a boolean expression. If the result of evaluating the HAVING clause is false, the group is discarded” (SQLite SELECT).

SQLite HAVING syntax

SELECT column, aggregate_function(column)
FROM table_name
WHERE row_condition
GROUP BY column
HAVING group_condition
ORDER BY column;

	WHERE	HAVING
Filters	Rows	Groups
Runs	Before `GROUP BY`	After `GROUP BY`
Can use `SUM()`, `COUNT()` …	No (error)	Yes

How to filter groups with HAVING

To show you how HAVING works, I will use an orders table with eight orders from three customers:

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_date TEXT NOT NULL,
  order_value INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1, 1, '2026-01-01', 100),
  (2, 2, '2026-01-15', 50),
  (3, 1, '2026-02-01', 200),
  (4, 3, '2026-02-15', 75),
  (5, 2, '2026-03-01', 60),
  (6, 1, '2026-03-15', 150),
  (7, 3, '2026-04-01', 125),
  (8, 2, '2026-04-15', 90);

Step 1: Group and aggregate

Start with the totals per customer, so you can see what you’re filtering:

sqlite> SELECT customer_id, COUNT(*) AS orders, SUM(order_value) AS total, AVG(order_value) AS average FROM orders GROUP BY customer_id;
customer_id  orders  total  average
-----------  ------  -----  ----------------
1            3       450    150.0
2            3       200    66.6666666666667
3            2       200    100.0

Step 2: Add HAVING with the aggregate

Keep customers whose orders add up to more than 250:

sqlite> SELECT customer_id, SUM(order_value) AS total FROM orders GROUP BY customer_id HAVING SUM(order_value) > 250;
customer_id  total
-----------  -----
1            450

Step 3: Use the alias instead

SQLite also accepts the column alias in HAVING, which saves repeating the expression:

sqlite> SELECT customer_id, SUM(order_value) AS total FROM orders GROUP BY customer_id HAVING total > 250;
customer_id  total
-----------  -----
1            450

Examples

These examples run against the same eight orders.

HAVING with AVG

> 100 leaves out customer 3, whose average is exactly 100:

sqlite> SELECT customer_id, AVG(order_value) AS average FROM orders GROUP BY customer_id HAVING AVG(order_value) > 100;
customer_id  average
-----------  -------
1            150.0

Use >= to include it:

sqlite> SELECT customer_id, AVG(order_value) AS average FROM orders GROUP BY customer_id HAVING AVG(order_value) >= 100;
customer_id  average
-----------  -------
1            150.0
3            100.0

HAVING with COUNT

Customers with at least three orders:

sqlite> SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id HAVING COUNT(*) >= 3;
customer_id  orders
-----------  ------
1            3
2            3

Several conditions

Combine aggregates with AND. Here: at least three orders, but none of them 150 or more:

sqlite> SELECT customer_id, COUNT(*) AS orders, MAX(order_value) AS biggest FROM orders GROUP BY customer_id HAVING COUNT(*) >= 3 AND MAX(order_value) < 150;
customer_id  orders  biggest
-----------  ------  -------
2            3       90

An aggregate that isn’t in the SELECT list

HAVING can use any aggregate, even one you don’t return. These are the customers whose smallest order was at least 75:

sqlite> SELECT customer_id FROM orders GROUP BY customer_id HAVING MIN(order_value) >= 75;
customer_id
-----------
1
3

WHERE and HAVING together

WHERE drops January orders first, then HAVING filters the February-to-April totals:

sqlite> SELECT customer_id, SUM(order_value) AS total FROM orders WHERE order_date >= '2026-02-01' GROUP BY customer_id HAVING SUM(order_value) > 100;
customer_id  total
-----------  -----
1            350
2            150
3            200

Sort and limit the groups

ORDER BY and LIMIT come after HAVING. The second sort column breaks the tie between customers 2 and 3:

sqlite> SELECT customer_id, SUM(order_value) AS total FROM orders GROUP BY customer_id HAVING total > 100 ORDER BY total DESC, customer_id LIMIT 2;
customer_id  total
-----------  -----
1            450
2            200

Edge cases

An aggregate in WHERE: SQLite rejects it. Move the condition to HAVING:

sqlite> SELECT customer_id, SUM(order_value) AS total FROM orders WHERE SUM(order_value) > 250 GROUP BY customer_id;
Error: in prepare, misuse of aggregate: SUM()
  r_id, SUM(order_value) AS total FROM orders WHERE SUM(order_value) > 250 GROUP
                                      error here ---^

HAVING without GROUP BY: since SQLite 3.39.0, this works when the query is an aggregate. Older versions reject it. The whole table is one group, so you get one row or none:

sqlite> SELECT COUNT(*) AS orders, SUM(order_value) AS total FROM orders HAVING SUM(order_value) > 500;
orders  total
------  -----
8       850
sqlite> SELECT COUNT(*) AS orders FROM orders HAVING COUNT(*) > 100;

HAVING on a query with no aggregates: SQLite 3.51.0 rejects it. Use WHERE for row filters:

sqlite> SELECT order_id, order_value FROM orders HAVING order_value > 100;
Error: in prepare, HAVING clause on a non-aggregate query

Conclusion

Use WHERE for conditions on single rows and HAVING for conditions on groups. In SQLite you can refer to a SELECT alias in HAVING, which keeps long aggregates readable.