SQL GROUP BY and HAVING Explained: Beginner to Interview Guide

WHERE, GROUP BY, HAVING – Quick Guide

Core ideas

• WHERE filters rows.
• GROUP BY makes groups of rows.
• HAVING filters groups (after grouping).
• 👉 Rule that never changes:Every column in SELECT must be either:
  • in GROUP BY, or
  • an aggregate (SUM, COUNT, AVG, MAX, MIN).
• Execution order (simplified):
  FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY (→ LIMIT/OFFSET if supported)

WHERE vs HAVING

• WHERE filters rows (pre-aggregation).
• HAVING filters groups (post-aggregation).
• Prefer WHERE when possible (filters earlier, usually faster); use HAVING when you need to filter on aggregates.

Example using both together

Goal: Customers whose individual order is ≥ 100 and total spent > 250.

1
2
3
4
5

SELECT customer, SUM(amount) AS total_amount
FROM orders
WHERE amount >= 100          -- filter rows
GROUP BY customer            -- make groups
HAVING SUM(amount) > 250;    -- filter groups

Sample table to imagine

1
2
3
4
5
6
7
8
9

orders

| order_id | customer | amount |
| -------- | -------- | ------ |
| 1        | A        | 100    |
| 2        | A        | 200    |
| 3        | B        | 50     |
| 4        | B        | 150    |
| 5        | C        | 300    |

1️⃣ GROUP BY – “make groups”

Question: Total amount spent by each customer

1
2
3

SELECT customer, SUM(amount) AS total_amount
FROM orders
GROUP BY customer;

What happens:

• Rows are grouped by customer.
• SUM(amount) is calculated per group.

Result:

Rule to remember

Every column in SELECT must be either

• in GROUP BY, or
• an aggregate (SUM, COUNT, AVG, MAX, MIN).

2️⃣ HAVING – “filter the groups”

Question: Show only customers whose total amount is greater than 250

❌ Wrong (common mistake):

1
2
3
4

SELECT customer, SUM(amount) AS total_amount
FROM orders
WHERE SUM(amount) > 250      -- ❌ invalid
GROUP BY customer;

• This fails because WHERE runs before grouping, so aggregates are not available there.

✅ Correct:

1
2
3
4

SELECT customer, SUM(amount) AS total_amount
FROM orders
GROUP BY customer
HAVING SUM(amount) > 250;

1
2
3
4
5
6

Result:

| customer | total_amount |
| -------- | ------------ |
| A        | 300          |
| C        | 300          |

• HAVING works after GROUP BY, so it can use aggregates like SUM(amount).

Common questions

Q1: Can we use HAVING without GROUP BY?

✔️ Yes (but uncommon):

1
2
3

SELECT SUM(amount) AS total_amount
FROM orders
HAVING SUM(amount) > 500;

• Without GROUP BY, all rows form one implicit group, and HAVING filters that single aggregated result.

Q2: Can we use WHERE with GROUP BY?

✔️ Yes — and often together.

• Use WHERE to filter raw rows before grouping.
• Use HAVING to filter on aggregate results after grouping.

Q3: Why can’t we use aggregate functions in WHERE?

Because WHERE executes before GROUP BY, so aggregate values like SUM(amount) or COUNT(*) do not exist yet at that stage.