SQL (PostgreSQL, MySQL, SQLite) Module 4 – Aggregates and GROUP BY

HAVING in SQL: filtering groups

How HAVING keeps or drops whole groups after GROUP BY, when a condition belongs in WHERE instead, and which engines let HAVING use a column alias.

Beginner
15 minutes
Examples run with sql.js 1.14.2
By MySmartCoPilot

What you will learn

Filter groups with HAVING
Decide whether a condition belongs in WHERE or HAVING
Use aliases in HAVING only where the engine allows it

Before you start

GROUP BY in SQL: one row per group

On this page

GROUP BY gives one row per group. Often you only want some of those rows: the customers with three or more orders, the cities above a sales target, the products that sold at least ten times. The condition is about the group, about a count or a total, so it cannot go in WHERE. It goes in HAVING, which filters groups the way WHERE filters rows.

Each script below builds the same table of ten orders and then queries it with SQLite 3.49.1, which sql.js 1.14.2 runs in your browser.

Keep the groups that pass

Customers with at least three orders SQL · three_or_more.sql

-- Ten orders; status is 'paid' or 'cancelled', amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Asha',   'Pune',   'paid',      45000),
  (2,  'Vikram', 'Kochi',  'paid',      12000),
  (3,  'Asha',   'Pune',   'cancelled', 30000),
  (4,  'Meera',  'Jaipur', 'paid',       8000),
  (5,  'Asha',   'Pune',   'paid',      99000),
  (6,  'Vikram', 'Kochi',  'paid',      15000),
  (7,  'Tenzin', 'Pune',   'paid',      52000),
  (8,  'Vikram', 'Kochi',  'cancelled',  7000),
  (9,  'Meera',  'Jaipur', 'paid',      26000),
  (10, 'Asha',   'Pune',   'paid',      18000);

-- Customers with at least three orders: HAVING keeps or drops whole groups.
SELECT customer, count(*) AS n_orders
FROM orders
GROUP BY customer
HAVING count(*) >= 3
ORDER BY n_orders DESC;

Output

┌──────────┬──────────┐
│ customer │ n_orders │
├──────────┼──────────┤
│ Asha     │ 4        │
│ Vikram   │ 3        │
└──────────┴──────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < three_or_more.sql

The query groups the orders by customer, and HAVING count(*) >= 3 then looks at each group’s count. Asha (4 orders) and Vikram (3) pass. Meera’s and Tenzin’s groups are dropped whole, as if they had never been formed. HAVING comes after GROUP BY in the query and runs after it too, so it can use any aggregate of the group, even one that is not in the SELECT list.

WHERE first, HAVING second

Most real questions need both filters. “Which customers have paid more than 500 rupees in total?” first throws away the cancelled orders, a condition on each row, and then keeps the customers whose total is high enough, a condition on each group:

A row filter and a group filter in one query SQL · where_and_having.sql

-- Ten orders; status is 'paid' or 'cancelled', amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Asha',   'Pune',   'paid',      45000),
  (2,  'Vikram', 'Kochi',  'paid',      12000),
  (3,  'Asha',   'Pune',   'cancelled', 30000),
  (4,  'Meera',  'Jaipur', 'paid',       8000),
  (5,  'Asha',   'Pune',   'paid',      99000),
  (6,  'Vikram', 'Kochi',  'paid',      15000),
  (7,  'Tenzin', 'Pune',   'paid',      52000),
  (8,  'Vikram', 'Kochi',  'cancelled',  7000),
  (9,  'Meera',  'Jaipur', 'paid',      26000),
  (10, 'Asha',   'Pune',   'paid',      18000);

-- WHERE removes rows before grouping; HAVING removes groups after it.
SELECT customer, sum(amount_paise) / 100.0 AS paid_rupees
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING sum(amount_paise) > 50000
ORDER BY paid_rupees DESC;

Output

┌──────────┬─────────────┐
│ customer │ paid_rupees │
├──────────┼─────────────┤
│ Asha     │ 1620.0      │
│ Tenzin   │ 520.0       │
└──────────┴─────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < where_and_having.sql

WHERE filters rows, HAVING filters groups

Text description of the diagram

The diagram follows the lesson's query from top to bottom. Amounts are in rupees.

The orders table has 10 rows: 8 paid and 2 cancelled.
WHERE status = 'paid' looks at each row and keeps the 8 paid ones.
GROUP BY customer puts those 8 rows into 4 groups, with paid totals of 1620 for Asha, 520 for Tenzin, 340 for Meera and 270 for Vikram.
HAVING sum(amount_paise) > 50000 looks at each group and keeps those with more than 500 rupees: Asha and Tenzin. Meera and Vikram are dropped as whole groups.
The result, sorted by the total, is Asha with 1620.0 and Tenzin with 520.0.

The order matters. WHERE runs on rows before they are grouped, so the cancelled orders never reach sum(). HAVING runs on groups after the aggregates are computed, so it can compare each total with 50000 paise.

When a condition could go in either clause

A condition on a column you group by makes sense in both places, and gives the same result:

The same filter in WHERE and in HAVING SQL · where_or_having.sql

-- Ten orders; status is 'paid' or 'cancelled', amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Asha',   'Pune',   'paid',      45000),
  (2,  'Vikram', 'Kochi',  'paid',      12000),
  (3,  'Asha',   'Pune',   'cancelled', 30000),
  (4,  'Meera',  'Jaipur', 'paid',       8000),
  (5,  'Asha',   'Pune',   'paid',      99000),
  (6,  'Vikram', 'Kochi',  'paid',      15000),
  (7,  'Tenzin', 'Pune',   'paid',      52000),
  (8,  'Vikram', 'Kochi',  'cancelled',  7000),
  (9,  'Meera',  'Jaipur', 'paid',      26000),
  (10, 'Asha',   'Pune',   'paid',      18000);

-- A condition on a grouped column can go in either clause; the result is the same.
SELECT city, count(*) AS n_orders FROM orders WHERE city = 'Pune' GROUP BY city;
SELECT city, count(*) AS n_orders FROM orders GROUP BY city HAVING city = 'Pune';

Output

┌──────┬──────────┐
│ city │ n_orders │
├──────┼──────────┤
│ Pune │ 5        │
└──────┴──────────┘
┌──────┬──────────┐
│ city │ n_orders │
├──────┼──────────┤
│ Pune │ 5        │
└──────┴──────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < where_or_having.sql

Prefer WHERE. The query then says plainly that the condition is about rows, and the other cities’ rows are gone before any grouping starts. Some engines make that move for you: since version 3.19.0, SQLite shifts a HAVING condition that uses only grouped columns into WHERE, so the two queries above do exactly the same work in your browser. MySQL does not; its manual says HAVING is applied close to the end, without optimisation, and asks you to keep such conditions out of it. The rule of thumb: a condition that does not use an aggregate belongs in WHERE; a condition on count, sum, avg, min or max of the group belongs in HAVING.

A common mistake: a row condition in HAVING

The rule of thumb matters most when the condition uses a column you did not group by. Here the goal is the number of paid orders per customer, and the status test has wandered into HAVING:

A row condition in the wrong clause SQL · having_bare_column.sql

-- Ten orders; status is 'paid' or 'cancelled', amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Asha',   'Pune',   'paid',      45000),
  (2,  'Vikram', 'Kochi',  'paid',      12000),
  (3,  'Asha',   'Pune',   'cancelled', 30000),
  (4,  'Meera',  'Jaipur', 'paid',       8000),
  (5,  'Asha',   'Pune',   'paid',      99000),
  (6,  'Vikram', 'Kochi',  'paid',      15000),
  (7,  'Tenzin', 'Pune',   'paid',      52000),
  (8,  'Vikram', 'Kochi',  'cancelled',  7000),
  (9,  'Meera',  'Jaipur', 'paid',      26000),
  (10, 'Asha',   'Pune',   'paid',      18000);

-- Meant: the number of paid orders per customer.
-- Wrong: status is not grouped, so HAVING reads it from one arbitrary row of each group.
SELECT customer, count(*) AS n_orders
FROM orders
GROUP BY customer
HAVING status = 'paid'
ORDER BY customer;

-- Right: filter the rows in WHERE, before they are grouped.
SELECT customer, count(*) AS n_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer
ORDER BY customer;

Output

┌──────────┬──────────┐
│ customer │ n_orders │
├──────────┼──────────┤
│ Asha     │ 4        │
│ Meera    │ 2        │
│ Tenzin   │ 1        │
│ Vikram   │ 3        │
└──────────┴──────────┘
┌──────────┬──────────┐
│ customer │ n_orders │
├──────────┼──────────┤
│ Asha     │ 3        │
│ Meera    │ 2        │
│ Tenzin   │ 1        │
│ Vikram   │ 2        │
└──────────┴──────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < having_bare_column.sql

The first query looks plausible and runs without an error, but its counts are wrong: Asha has 4 orders in it, and only 3 of them are paid. HAVING can only keep or drop a whole group, so it never removes the cancelled orders from a count. And status is not grouped, so SQLite checks it against one arbitrary row of each group; a customer whose chosen row happened to be cancelled would vanish entirely. The second query filters the rows in WHERE and gets 3 for Asha.

PostgreSQL rejects the first query because status is neither grouped nor aggregated. MySQL rejects it too, as an unknown column: its HAVING can use only grouped columns, columns of the SELECT list and aggregates, and status is none of them. SQLite runs it, which is why this mistake is easy to miss in SQLite.

Aliases in HAVING

It is tempting to give the aggregate a name in the SELECT list and use that name in HAVING:

An alias in HAVING, and the portable form SQL · having_alias.sql

-- Ten orders; status is 'paid' or 'cancelled', amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Asha',   'Pune',   'paid',      45000),
  (2,  'Vikram', 'Kochi',  'paid',      12000),
  (3,  'Asha',   'Pune',   'cancelled', 30000),
  (4,  'Meera',  'Jaipur', 'paid',       8000),
  (5,  'Asha',   'Pune',   'paid',      99000),
  (6,  'Vikram', 'Kochi',  'paid',      15000),
  (7,  'Tenzin', 'Pune',   'paid',      52000),
  (8,  'Vikram', 'Kochi',  'cancelled',  7000),
  (9,  'Meera',  'Jaipur', 'paid',      26000),
  (10, 'Asha',   'Pune',   'paid',      18000);

-- SQLite (and MySQL) accept the alias n_orders in HAVING; PostgreSQL does not.
SELECT city, count(*) AS n_orders
FROM orders
GROUP BY city
HAVING n_orders >= 3;

-- Portable: repeat the aggregate.
SELECT city, count(*) AS n_orders
FROM orders
GROUP BY city
HAVING count(*) >= 3;

Output

┌───────┬──────────┐
│ city  │ n_orders │
├───────┼──────────┤
│ Kochi │ 3        │
│ Pune  │ 5        │
└───────┴──────────┘
┌───────┬──────────┐
│ city  │ n_orders │
├───────┼──────────┤
│ Kochi │ 3        │
│ Pune  │ 5        │
└───────┴──────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < having_alias.sql

SQLite and MySQL accept HAVING n_orders >= 3; MySQL’s manual describes it as its own extension of the standard. PostgreSQL does not. It lets GROUP BY and ORDER BY refer to a name from the SELECT list, but in HAVING, as in WHERE, it looks only for columns of the tables, finds no n_orders there and reports that the column does not exist. For SQL that runs everywhere, repeat the aggregate, as in the second query.

HAVING without GROUP BY

HAVING also works without GROUP BY. Then all the rows that pass WHERE form a single group, and HAVING decides whether the query returns its one summary row or nothing:

One group or none SQL · having_without_group_by.sql

-- Ten orders; status is 'paid' or 'cancelled', amounts are in paise.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  status       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO orders VALUES
  (1,  'Asha',   'Pune',   'paid',      45000),
  (2,  'Vikram', 'Kochi',  'paid',      12000),
  (3,  'Asha',   'Pune',   'cancelled', 30000),
  (4,  'Meera',  'Jaipur', 'paid',       8000),
  (5,  'Asha',   'Pune',   'paid',      99000),
  (6,  'Vikram', 'Kochi',  'paid',      15000),
  (7,  'Tenzin', 'Pune',   'paid',      52000),
  (8,  'Vikram', 'Kochi',  'cancelled',  7000),
  (9,  'Meera',  'Jaipur', 'paid',      26000),
  (10, 'Asha',   'Pune',   'paid',      18000);

-- No GROUP BY: all the rows that pass WHERE form one group.
SELECT count(*) AS paid_orders, sum(amount_paise) / 100.0 AS paid_rupees
FROM orders
WHERE status = 'paid'
HAVING count(*) >= 5;

-- The same query with a stricter HAVING returns no row at all; counting its rows shows it.
SELECT count(*) AS rows_returned
FROM (SELECT count(*) FROM orders WHERE status = 'paid' HAVING count(*) >= 50);

Output

┌─────────────┬─────────────┐
│ paid_orders │ paid_rupees │
├─────────────┼─────────────┤
│ 8           │ 2750.0      │
└─────────────┴─────────────┘
┌───────────────┐
│ rows_returned │
├───────────────┤
│ 0             │
└───────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < having_without_group_by.sql

There are 8 paid orders, so the first query passes count(*) >= 5 and returns its row. With >= 50 the same query returns no row at all; the second query counts its rows to show that. This is occasionally handy for a check such as “report the total only if there are enough orders to be meaningful”.

Version note

SQLite allows HAVING without GROUP BY from version 3.39.0; older versions reject it. The browser’s SQLite 3.49.1 runs it, and so do PostgreSQL and MySQL.

SQLite Online (SQL Playground) Filter the groups of your own CSV file with HAVING: import it as a table, and SQLite runs in your browser, as in this lesson.

Key takeaways

WHERE filters rows before they are grouped; HAVING filters groups after the aggregates are computed.
Conditions on aggregates (count(*) >= 3, sum(amount) > 50000) go in HAVING; everything else goes in WHERE, where it is clearer and usually cheaper (SQLite moves a HAVING condition on grouped columns there by itself; MySQL does not).
A row condition in HAVING keeps or drops whole groups and never removes rows from a count. SQLite runs such a query on an arbitrary row of each group; PostgreSQL and MySQL reject it.
SQLite and MySQL accept an output alias in HAVING, PostgreSQL does not; repeating the aggregate works everywhere.
Without GROUP BY, HAVING treats all the rows as one group and returns one row or none.

Exercise

Exercise · Easy · SQL

Cities with enough paying customers

The table payments has one row per payment attempt:

id: the payment's number;
customer_id: who paid;
city: the customer's city;
status: paid, cancelled or refunded;
amount_paise: the amount in paise (100 paise make a rupee, so 50,000 rupees are 5,000,000 paise).

Write a query that returns the cities where at least five different customers have paid and where the paid amounts add up to more than 50,000 rupees. Only payments whose status is paid count, for both conditions. Return three columns:

city;
customers: how many different customers paid in that city;
paid_rupees: the total paid in that city, in rupees.

The rows may come in any order.

Starter code · busy_cities.sql

-- Cities with at least five different paying customers and more than
-- 50,000 rupees (5,000,000 paise) paid in total.
SELECT city,
       count(*) AS customers,
       sum(amount_paise) / 100.0 AS paid_rupees
FROM payments
GROUP BY city;

The sample tests · tests.yaml

# Sample tests: each one builds a small payments table, runs your query on it and compares the rows it returns (in
# any order).
tests:
  - name: keeps a city only when both conditions hold
    setup: |
      CREATE TABLE payments (id INTEGER PRIMARY KEY, customer_id INTEGER, city TEXT, status TEXT, amount_paise INTEGER);
      INSERT INTO payments VALUES
        (1,  101, 'Pune',   'paid',      1500000),
        (2,  102, 'Pune',   'paid',       900000),
        (3,  103, 'Pune',   'paid',      1200000),
        (4,  104, 'Pune',   'paid',       700000),
        (5,  105, 'Pune',   'paid',      1100000),
        (6,  106, 'Pune',   'paid',       800000),
        (7,  201, 'Kochi',  'paid',       800000),
        (8,  202, 'Kochi',  'paid',       800000),
        (9,  203, 'Kochi',  'paid',       800000),
        (10, 204, 'Kochi',  'paid',       800000),
        (11, 205, 'Kochi',  'paid',       800000),
        (12, 206, 'Kochi',  'cancelled', 2000000),
        (13, 301, 'Jaipur', 'paid',      3000000),
        (14, 302, 'Jaipur', 'paid',      3000000),
        (15, 303, 'Jaipur', 'paid',      3000000);
    expected:
      columns: [city, customers, paid_rupees]
      rows:
        - ['Pune', 6, 62000.0]
  - name: counts each customer once and needs more than 50,000 rupees
    setup: |
      CREATE TABLE payments (id INTEGER PRIMARY KEY, customer_id INTEGER, city TEXT, status TEXT, amount_paise INTEGER);
      INSERT INTO payments VALUES
        (1,  401, 'Delhi',   'paid', 2000000),
        (2,  401, 'Delhi',   'paid', 1500000),
        (3,  402, 'Delhi',   'paid', 1500000),
        (4,  403, 'Delhi',   'paid', 1500000),
        (5,  404, 'Delhi',   'paid', 1500000),
        (6,  501, 'Chennai', 'paid', 1000000),
        (7,  502, 'Chennai', 'paid', 1000000),
        (8,  503, 'Chennai', 'paid', 1000000),
        (9,  504, 'Chennai', 'paid', 1000000),
        (10, 505, 'Chennai', 'paid', 1000000),
        (11, 601, 'Mumbai',  'paid', 1000000),
        (12, 602, 'Mumbai',  'paid', 1000000),
        (13, 603, 'Mumbai',  'paid', 1000000),
        (14, 604, 'Mumbai',  'paid', 1000000),
        (15, 605, 'Mumbai',  'paid', 1000050);
    expected:
      columns: [city, customers, paid_rupees]
      rows:
        - ['Mumbai', 5, 50000.5]
  - name: leaves out payments that were not paid
    setup: |
      CREATE TABLE payments (id INTEGER PRIMARY KEY, customer_id INTEGER, city TEXT, status TEXT, amount_paise INTEGER);
      INSERT INTO payments VALUES
        (1,  701, 'Hyderabad', 'paid',      1200000),
        (2,  702, 'Hyderabad', 'paid',      1200000),
        (3,  703, 'Hyderabad', 'paid',      1200000),
        (4,  704, 'Hyderabad', 'paid',      1200000),
        (5,  705, 'Hyderabad', 'paid',      1200000),
        (6,  706, 'Hyderabad', 'refunded',  3000000),
        (7,  801, 'Lucknow',   'paid',      1500000),
        (8,  802, 'Lucknow',   'paid',      1500000),
        (9,  803, 'Lucknow',   'paid',      1500000),
        (10, 804, 'Lucknow',   'paid',      1500000),
        (11, 805, 'Lucknow',   'cancelled', 1500000);
    expected:
      columns: [city, customers, paid_rupees]
      rows:
        - ['Hyderabad', 5, 60000.0]

A hint

There are two kinds of condition here. The status is about each row, so it belongs in WHERE. The number of customers and the total are about each city's group, so they belong in HAVING, joined with AND. count(DISTINCT customer_id) counts a customer who paid twice only once.

The sample tests run on this device, in your browser (sql.js): nothing is sent to mysmartcopilot.com. The first run downloads SQLite (about 0.7 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

References

PostgreSQL documentation: Table Expressions (GROUP BY and HAVING) (The PostgreSQL Global Development Group)
PostgreSQL tutorial: Aggregate Functions (The PostgreSQL Global Development Group)
PostgreSQL documentation: SELECT (The PostgreSQL Global Development Group)
SQLite: The SELECT statement (SQLite)
SQLite: Release History (versions 3.19.0 and 3.39.0) (SQLite)
MySQL 8.4 Reference Manual: SELECT Statement (Oracle Corporation)
MySQL 8.4 Reference Manual: MySQL Handling of GROUP BY (Oracle Corporation)

Report a problem with this lesson

Your country

HAVING in SQL: filtering groups

What you will learn

Before you start

Keep the groups that pass

WHERE first, HAVING second

When a condition could go in either clause

A common mistake: a row condition in HAVING

Aliases in HAVING

HAVING without GROUP BY

Key takeaways

Exercise

Cities with enough paying customers

Results of the sample tests

Check yourself

References

Related tools