SQL for Product Managers
Aggregating Data: GROUP BY, COUNT, SUM, AVG
This is the lesson that turns SQL from "look up individual rows" into "answer a real product question." Almost every metric you'll ever report comes from this pattern.
Aggregate functions collapse many rows into one summary number.
Function | What it does | Example |
|---|---|---|
COUNT(*) | How many rows | How many orders were placed |
COUNT(DISTINCT column) | How many unique values | How many distinct users placed an order |
SUM(column) | Total | Total revenue |
AVG(column) | Average | Average order value |
MIN(column) / MAX(column) | Smallest / largest value | Earliest signup date, largest single order |
Used alone, these summarize an entire table into one number. Used with GROUP BY, they summarize the table into one number per group, which is where the real product analysis begins.
SELECT plan, COUNT(*) AS num_users
FROM users
GROUP BY plan;
This answers: how many users are on each plan. GROUP BY plan tells the database "collapse all rows into one bucket per distinct plan value, then apply the aggregate function within each bucket."
A slightly richer version:
SELECT plan, COUNT(*) AS num_orders, SUM(amount) AS total_revenue, AVG(amount) AS avg_order_value
FROM orders
JOIN users ON orders.user_id = users.id
GROUP BY plan
ORDER BY total_revenue DESC;
This answers: for each plan, how many orders, how much total revenue, and what's the average order value, sorted by the plan bringing in the most revenue. (We'll cover that JOIN properly in the next lesson; for now, notice the shape.)
THE RULE THAT CONFUSES EVERYONE AT FIRST
Every column in your SELECT list must either be inside an aggregate function (COUNT, SUM, etc.) or listed in your GROUP BY. If you try to select a raw column that isn't grouped, most databases will either error or, worse on some systems, silently pick an arbitrary value for it. This is the single most common beginner SQL error, and it's actually the database protecting you from a genuinely ambiguous question: if you're grouping by plan, and you also ask for individual email addresses, which email address should represent the whole "pro" group? There isn't a correct answer, so don't ask the question that way.
YOUR MOVE THIS WEEK
Write a query that answers: how many users signed up, grouped by month. You'll need GROUP BY on a date part rather than the raw date, which we'll cover properly in Lesson 3.4, but try it now with what you know, even if the date grouping is a little clumsy. Getting it approximately right yourself teaches you more than reading a clean answer.