Aggregating: COUNT, SUM, GROUP BY

Sessions b1 and b2 gave you rows - filtered, sorted, but still one line per record. Leadership rarely wants 33 order rows; they want "how many orders?" and "how much revenue?". Aggregate functions squeeze many rows into a single number, and GROUP BY repeats that squeeze once per group so you get "orders per country" or "average price per category". That is the leap from data to insight.

An aggregate function takes a whole column of values and returns one number. COUNT tallies rows, SUM adds them up, AVG averages, MIN and MAX find the extremes. Applied to a whole table with no grouping, they collapse every row into a single summary line - the simplest form of a report.

SELECT COUNT(*) AS orders
FROM orders;

SELECT ROUND(AVG(price),2) AS avg_price
FROM products;

SELECT MIN(price) AS cheapest, MAX(price) AS priciest
FROM products;

One number for the whole table is a start, but the real questions are "per country", "per category", "per month". GROUP BY sorts rows into buckets by a column, then runs the aggregate once per bucket. The rule of thumb: whatever you put after GROUP BY is the "per what" of your question.

SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY customers DESC;

SELECT category, ROUND(AVG(price),2) AS avg_price
FROM products
GROUP BY category;

Two finishing tools. HAVING filters groups after aggregation - the counterpart to WHERE, which filters rows before. And DISTINCT strips duplicates, which is how you count unique things: how many different countries, not how many customers.

SELECT plan, COUNT(*) AS n
FROM customers
GROUP BY plan
HAVING COUNT(*) >= 3;

SELECT COUNT(DISTINCT country) AS countries
FROM customers;

Daybreak's founder asks the big one: "Which products make us the most money?" Revenue lives in order_items, where each line is a quantity times a unit_price. You have not learned joins yet (that is b4), so we will stay inside one table and aggregate by product_id. It is the most useful query in the whole session.

SELECT product_id, SUM(quantity*unit_price) AS revenue
FROM order_items
GROUP BY product_id
ORDER BY revenue DESC;

No scaffolding now. Each editor answers one aggregation question - run it, tweak it, break it, fix it. The run-fix-run loop is the whole skill.

SELECT channel, COUNT(*) AS orders
FROM orders
GROUP BY channel
ORDER BY orders DESC;

SELECT category, COUNT(*) AS products
FROM products
GROUP BY category
ORDER BY products DESC;

SELECT COUNT(DISTINCT customer_id) AS buyers
FROM orders;

• Count how many orders fall into each status (completed, refunded, cancelled). Which dominates?
• Find the total quantity sold per product with SUM(quantity) and GROUP BY product_id, biggest first.
• Using HAVING, list only the countries with two or more customers.
• Compute the average monthly_qty across the subscriptions table with AVG.
• Bring one "per group" question about Daybreak that also needs a product or customer name to session b4 - that is exactly the gap joins fill.

This session teaches the aggregation core of the major free SQL curricula, run on live Daybreak data instead of screenshots. Certificates and graded problem sets stay on the official sites (linked below). This page covers: