Skip to content

Intermediate · SQL in depth · topic 22 of 54 · 20 min

GROUP BY and HAVING

Explanation

GROUP BY splits rows into groups that share a value, like one group per city, and an aggregate such as SUM or COUNT then gives one answer per group. WHERE filters rows before grouping; HAVING filters the groups after. Every column you select must either be in GROUP BY or be inside an aggregate.

One answer per group

In the last module SUM(amount) gave you one number for the whole table. Often you want one number per something: sales per city, orders per customer, average marks per subject. GROUP BY does that. It puts rows with the same value into one group, then each aggregate (COUNT, SUM, AVG, MIN, MAX) runs once per group.

Think of it like this: A kirana shop owner at closing time has a pile of bills. She sorts them into small heaps, one per customer, then adds up each heap. Sorting into heaps is GROUP BY; adding up each heap is SUM. Throwing away torn bills before sorting is WHERE; keeping only the heaps above 1,000 rupees is HAVING.
sql
SELECT c.city, COUNT(*) AS orders, SUM(o.amount) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.city
ORDER BY total DESC;
One row per city: how many orders and how much money.
cityorderstotal
Mumbai34509
Delhi21650
Kochi21260
Chennai1799

Grouping by several columns

GROUP BY city, status makes one group for each different pair: (Mumbai, delivered), (Mumbai, pending) and so on. Use it for reports like "orders per month per status" or "deliveries per restaurant per status".

sql
SELECT c.city, o.status, COUNT(*) AS orders
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.city, o.status
ORDER BY c.city, o.status;
Each different (city, status) pair becomes its own group.

WHERE or HAVING?

WHERE looks at single rows, before any grouping, so it cannot use SUM or COUNT. HAVING looks at whole groups, after grouping, so it can. A query can use both: WHERE throws away cancelled orders, then HAVING keeps only cities whose total is more than 1,000 rupees.

sql
SELECT c.city, SUM(o.amount) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status <> 'cancelled'
GROUP BY c.city
HAVING SUM(o.amount) > 1000
ORDER BY total DESC;
WHERE filters rows, HAVING filters groups.
  1. FROM and JOIN: collect the rows
  2. WHERE: drop rows you do not want
  3. GROUP BY: put the rest into groups
  4. HAVING: drop groups you do not want
  5. SELECT: work out the output columns
  6. ORDER BY and LIMIT: sort and cut
Tip: This is the order the database thinks in, not the order you type. It explains why WHERE cannot see SUM (groups do not exist yet) and why ORDER BY can usually use a column alias like total (SELECT has already run).

The golden rule

After grouping there is only one output row per group. So each selected column must either be in GROUP BY (the same for the whole group) or be inside an aggregate (one value made from the whole group). SELECT city, name, SUM(amount) ... GROUP BY city breaks the rule: a city has many names, so which one should SQL show?

Careful: PostgreSQL rejects that query with "column must appear in the GROUP BY clause or be used in an aggregate function". SQLite and old MySQL quietly pick a random row's value instead. The query runs but the answer can be wrong, so follow the rule even where it is not enforced.
Tip: COUNT(*) counts rows in the group. COUNT(column) counts only rows where that column is not NULL. After a LEFT JOIN, use COUNT(o.id) so a customer with no orders shows 0, not 1.

Watch the lesson

GROUP BY and HAVING · 1/6

GROUP BY and HAVING

Totals for a whole table are useful, but reports need totals per city, per day or per customer. That is GROUP BY.

Prefer a video by a teacher? Find free videos on “sql group by having explained tutorial” on YouTube ↗

In real life: A restaurant partner dashboard

A food delivery app shows each restaurant owner a dashboard: orders today, revenue this week, late deliveries per day. Every one of those numbers is a GROUP BY query. The operations team runs a similar one with HAVING to list restaurants whose late deliveries are more than a set number this week, so someone can call them before customers complain.

sql
SELECT r.name, COUNT(*) AS late_count
FROM deliveries d
JOIN restaurants r ON r.id = d.restaurant_id
WHERE d.status = 'late'
GROUP BY r.name
HAVING COUNT(*) >= 2
ORDER BY late_count DESC;
Restaurants with two or more late deliveries.

Practice (0/3 done)

Task 1 · Code · SQL

Tables: customers(id, name, city) and orders(id, customer_id, order_date, amount, status). Show each city and its total order amount, ignoring cancelled orders. Keep only cities whose total is more than 1000 rupees. Highest total first.

Task 2 · Code · SQL

Tables: restaurants(id, name, city) and deliveries(id, restaurant_id, status, minutes). Count deliveries for each restaurant name and status ('late' or 'on_time'). Show name, status and the count, sorted by name, then status.

Task 3 · Code · SQL

Tables: students(id, name, class) and marks(student_id, subject, score). Show each student's name and total score across all subjects, only for students whose total is at least 220. Highest total first.

Quick quiz

Question 1

1.Which clause keeps only cities whose SUM(amount) is above 1000?

Question 2

2.Why does PostgreSQL reject SELECT city, name, SUM(amount) FROM t GROUP BY city?

Question 3

3.You want to ignore cancelled orders before adding up totals per city. Where does status <> 'cancelled' go?

Question 4

4.GROUP BY city, status on data with 3 cities and 2 statuses can give at most how many rows?

Question 5

5.After customers LEFT JOIN orders, which count shows 0 for a customer with no orders?

Read more (free)

Pass the quiz (60% or more) or mark this topic as done when you are ready.

Your progress is saved on this device only.

Next: Subqueries, CTEs and window functions →

← Previous: Joins: combining tables