SQL GROUP BY and HAVING Explained with Examples

Quick Answer

GROUP BY collapses rows that share the same value into one row per group, so you can run aggregate functions like COUNT, SUM, and AVG on each group. WHERE filters individual rows before grouping, while HAVING filters the grouped results after aggregation. Use WHERE for row conditions and HAVING when your condition depends on an aggregate.

What Does SQL GROUP BY Do?

When you store data in a table, you often have many rows that belong together — several orders from the same city, many sales by the same customer, repeated entries for the same day. SQL GROUP BY lets you collapse those related rows into a single summary row per group, so you can answer questions like “how many orders per city?” or “what is the total revenue per customer?”

On its own, grouping is only half the story. You pair GROUP BY with an aggregate function (such as COUNT or SUM) that squeezes each group of rows down to one value. In this tutorial we build up from a plain query to a full report, and clear up the confusion that trips up almost every beginner: the difference between WHERE and HAVING.

If you want to practise alongside this post, our free SQL course lets you run every query in your browser.

Our Sample Orders Table

Every example below uses one small orders table. You can paste this into any SQL database — MySQL, PostgreSQL, SQLite, or SQL Server — to follow along.

CREATE TABLE orders (
  order_id    INT,
  customer_id INT,
  city        VARCHAR(50),
  amount      INT,
  status      VARCHAR(20),
  order_date  DATE
);

INSERT INTO orders VALUES
  (1, 101, 'Mumbai',    1200, 'completed', '2026-01-05'),
  (2, 102, 'Delhi',      800, 'completed', '2026-01-06'),
  (3, 101, 'Mumbai',    2000, 'completed', '2026-01-08'),
  (4, 103, 'Bengaluru',  500, 'cancelled', '2026-01-09'),
  (5, 104, 'Delhi',     1500, 'completed', '2026-01-10'),
  (6, 105, 'Mumbai',     300, 'completed', '2026-01-11'),
  (7, 106, 'Bengaluru', 2500, 'completed', '2026-01-12');

Seven rows, three cities, and a mix of completed and cancelled orders — enough to show every idea clearly.

Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

An aggregate function takes many rows and returns a single value. These five cover almost everything you will need day to day:

• COUNT() — how many rows (or non-NULL values).
• SUM() — the total of a numeric column.
• AVG() — the average value.
• MIN() — the smallest value.
• MAX() — the largest value.

Without GROUP BY, an aggregate treats the whole table as one big group:

SELECT COUNT(*)    AS total_orders,
       SUM(amount) AS total_amount,
       AVG(amount) AS avg_amount
FROM orders;

This returns a single row: 7 orders, a total of 8800, and an average of roughly 1257. The moment you add GROUP BY, that same calculation runs once per group instead of once for the whole table.

Grouping Rows with GROUP BY

To count orders per city, group by the city column and let COUNT do the work:

SELECT city, COUNT(*) AS orders
FROM orders
GROUP BY city;

The database gathers all rows that share a city, then reports one row for each:

city       | orders
-----------+-------
Bengaluru  | 2
Delhi      | 2
Mumbai     | 3

You are not limited to one aggregate or one column. This query builds a small per-city report and groups by two columns at once:

SELECT city, status,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
GROUP BY city, status;

Grouping by city, status creates one row for each unique combination — for example, Bengaluru/completed and Bengaluru/cancelled become separate groups. The golden rule to remember: every column in your SELECT list must either be inside an aggregate function or listed in GROUP BY.

WHERE vs HAVING: The Key Difference

This is the part that trips up almost every beginner. Both WHERE and HAVING filter data, but they act at different stages:

• WHERE filters individual rows before they are grouped. It cannot see aggregate results.
• HAVING filters whole groups after aggregation. It is the only place you can test the result of COUNT, SUM, AVG, and friends.

SQL runs the clauses in a fixed order, which explains the rule above:

FROM  ->  WHERE  ->  GROUP BY  ->  HAVING  ->  SELECT  ->  ORDER BY

Because WHERE happens before GROUP BY, it filters raw rows (for example, keeping only completed orders). Because HAVING happens after, it can filter on a total (for example, keeping only cities whose revenue crosses a threshold).

Why “Partial” for HAVING on plain columns? You can reference a grouped column in HAVING, but for filtering non-aggregated columns you should use WHERE — it is clearer and lets the database skip rows earlier, which is faster.

A Full Worked Query

Now let's combine everything into one realistic report: “For completed orders only, show each city's order count, revenue, and average order value — but only for cities with revenue above 2400, highest revenue first.”

SELECT city,
       COUNT(*)           AS completed_orders,
       SUM(amount)        AS revenue,
       ROUND(AVG(amount)) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY city
HAVING SUM(amount) > 2400
ORDER BY revenue DESC;

Read it the way the database runs it:

1. WHERE drops the cancelled Bengaluru order, leaving six rows.
2. GROUP BY gathers the survivors into Mumbai, Delhi, and Bengaluru groups.
3. HAVING keeps only groups whose SUM(amount) is above 2400, so Delhi (2300) is removed.
4. ORDER BY sorts the remaining groups by revenue, highest first.

The result:

city       | completed_orders | revenue | avg_order_value
-----------+------------------+---------+----------------
Mumbai     | 3                | 3500    | 1167
Bengaluru  | 1                | 2500    | 2500

Notice how WHERE status = 'completed' and HAVING SUM(amount) > 2400 do two completely different jobs in the same query — that pairing is the heart of grouped reporting.

Common Gotchas and a Recommendation

A few traps catch beginners again and again:

• Don't put aggregates in WHERE. WHERE SUM(amount) > 2400 throws an error. Aggregate conditions belong in HAVING.
• Every non-aggregated SELECT column must be in GROUP BY. Strict databases (PostgreSQL, SQL Server, and MySQL with ONLY_FULL_GROUP_BY) reject queries that break this rule. Older MySQL might allow it but return an unpredictable value, so never rely on it.
• COUNT(*) vs COUNT(column). COUNT(*) counts every row; COUNT(column) skips rows where that column is NULL, so the two can return different numbers.
• NULLs group together. If a grouping column contains NULLs, all of them fall into one single group rather than being ignored.
• Alias visibility varies. To stay portable, repeat the expression in HAVING (HAVING SUM(amount) > 2400) instead of a SELECT alias, because not every database lets HAVING use an alias.

Recommendation: filter rows as early as you can with WHERE, group only the columns you actually need, and save HAVING for conditions that genuinely depend on an aggregate. That habit keeps your queries fast, correct, and easy to read.

Frequently Asked Questions