GROUP BY: Sort Into Buckets

Aggregates per group

Aggregates squash everything into one row. GROUP BY says "squash *per category* instead". You get one row for each distinct value of the grouped column.

Example: How many pizzas in each category?

SELECT category, COUNT(*) AS how_many FROM pizzas GROUP BY category;

Rule of thumb: every column in SELECT is either grouped or aggregated.

Any aggregate works

SUM, AVG, MIN, MAX all play nicely with GROUP BY. Add ORDER BY to make the report readable.

Example: Average price per category, priciest first

SELECT category, ROUND(AVG(price), 2) AS avg_price FROM pizzas GROUP BY category ORDER BY avg_price DESC;

You can ORDER BY an alias you created in SELECT.

HAVING: filter the groups

WHERE filters rows before grouping. HAVING filters groups after aggregating. You cannot put an aggregate in WHERE. That is exactly what HAVING is for.

Example: Categories with more than 2 pizzas

SELECT category, COUNT(*) AS n FROM pizzas GROUP BY category HAVING COUNT(*) > 2;

Try moving the condition into WHERE. SQLite will refuse, because aggregates do not exist yet at that stage.

WHERE and HAVING together

Filter rows first, group, then filter the groups. Full pipeline: WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.

Example: Customers who ordered 3+ pizzas in total, delivered only

SELECT customer_id, SUM(quantity) AS pizzas FROM orders WHERE delivered = 1 GROUP BY customer_id HAVING SUM(quantity) >= 3 ORDER BY pizzas DESC;

Undelivered orders are dropped before the sum happens.

Practice exercises

1. Customers per city

   For each city, count the customers. Return city and a column named customers.

2. Calories by category

   For each pizza category, show the maximum calories. Name it max_cal.

3. Busy chefs

   Count how many pizzas each chef created. Return chef_id and pizza_count, but only chefs with 2 or more pizzas.

4. Pizza popularity

   For each pizza_id in orders, sum the quantity as total_sold. Sort by total_sold descending, then by pizza_id ascending.

5. Cheap vegetarian categories

   Among vegetarian pizzas only, find each category whose average price is below 12. Return category and avg_price (rounded to 2 decimals).

Open this page in a browser to run your SQL and get instant, auto-graded feedback.

Boss battle

Take the GROUP BY quiz: 5 timed questions.