Intermediate
Question 1
Group the users table by city, and retrieve each city name along with the number of users.
📖 ExplanationReference
Aggregate functions
These are functions that combine multiple rows into a single value. They can be used on their own, but they really shine when combined with GROUP BY.
| Function | Meaning | Example |
|---|---|---|
| COUNT(*) | counts rows | COUNT(*) → 15 |
| SUM(col) | computes a total | SUM(price) → total of all product prices |
| AVG(col) | computes an average | AVG(price) → average price |
| MAX(col) | finds the maximum | MAX(price) → highest price |
| MIN(col) | finds the minimum | MIN(price) → lowest price |
Grouping with GROUP BY
This groups data by the values in a specified column and aggregates within each group. Questions like "how many people are in each city?" or "what's the average price per category?" can be written as a single query.
▼ What GROUP BY city looks like
Original data (users)
Tanaka → Tokyo
Suzuki → Osaka
Sato → Tokyo
Yamada → Nagoya
Nakamura → Tokyo
Suzuki → Osaka
Sato → Tokyo
Yamada → Nagoya
Nakamura → Tokyo
→
After GROUP BY city
Tokyo → COUNT=3
Osaka → COUNT=1
Nagoya → COUNT=1
Osaka → COUNT=1
Nagoya → COUNT=1
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;
⚠️ A common mistake: mixing in non-aggregated columns
If you're doing
If you're doing
GROUP BY city but write SELECT name, city, COUNT(*), that's an error. name has multiple values within a group, so it's ambiguous which one to return. You can only SELECT columns that are either in the GROUP BY, or wrapped in an aggregate function.
Filtering groups with HAVING
WHERE filters before grouping; HAVING filters after grouping. If you want to show "only cities with 2 or more people," use HAVING.
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) >= 2;JOIN + GROUP BY
SELECT u.city, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.city
ORDER BY order_count DESC;
💼 Where it's used in the real world
"Monthly sales totals," "sales ranking by product category," "number of purchases per user" — business dashboards and reports are almost always built with GROUP BY. It's the SQL syntax data analysts and marketers reach for the most.
"Monthly sales totals," "sales ranking by product category," "number of purchases per user" — business dashboards and reports are almost always built with GROUP BY. It's the SQL syntax data analysts and marketers reach for the most.