Stop 03 · SQL for Data AnalysisTopics · 3/10

GROUP BY & HAVING

In short

"Sales per city", "orders per month", "customers per plan": anything with 'per' is GROUP BY. It's the SQL version of a PivotTable, and you'll write it every day.

GROUP BY buckets rows into categories before aggregating (e.g. sales per region), and HAVING filters those aggregated results — the SQL equivalent of a PivotTable.

The jaadu in a white cap sorts tiffin boxes into crates by lid colour.

Think of it like this

The dabbawala sorting station

At the station, dabbawalas sort hundreds of tiffins into crates by area code. Then they count each crate. Only areas with enough tiffins get a separate handcart. GROUP BY makes the crates, the aggregate counts them, and HAVING decides which crates qualify.

Watch it happen

WHERE, GROUP BY, then HAVING

All orders

WHERE

GROUP BY

Aggregate

HAVING

1/5Start with every order row from the table.

The big ideas

What you need to know

One row per group

GROUP BY city returns one row for each distinct city, with aggregates computed inside each.

The golden rule

Every column in SELECT must either be in GROUP BY or inside an aggregate like SUM.

WHERE vs HAVING

WHERE filters rows before grouping. HAVING filters groups after aggregating.

Group by several columns

GROUP BY city, month gives one row per city-month pair.

See it in code

Walk through the code

sql

1SELECT2  city,3  COUNT(*)    AS orders,4  SUM(amount) AS revenue5FROM orders6WHERE status = 'delivered'7GROUP BY city8HAVING COUNT(*) >= 1009ORDER BY revenue DESC;

1/4city is grouped, the other two are aggregates. That follows the golden rule.

Revenue per city, only counting delivered orders, only for cities with 100 or more orders.

Save yourself the pain

Common mistakes

Oops

Using WHERE SUM(amount) > 10000.

Do this instead

Aggregates can't go in WHERE. Use HAVING SUM(amount) > 10000.

Oops

Selecting customer_name while grouping only by city.

Do this instead

Either add it to GROUP BY or wrap it in an aggregate. Otherwise the query errors or returns a random name.

Your mission

Pivot in SQL

~25 min

Quick check

You want cities whose average order is above ₹600. Where does the condition go?

Go deeper

1Write a query showing total sales grouped by region and month.
2Practice using HAVING to filter groups (e.g. only regions with sales over a threshold).
3Learn why WHERE can't filter on an aggregated value, but HAVING can.