GROUP BY Fundamentals

One Number Per Group

In the last lesson, you got a single total for the whole table. Useful, but limited. Real business questions usually need breakdowns: revenue by month, customers by region, headcount by department.

GROUP BY is how you get there. It splits your data into groups, then applies your aggregate function to each group separately.

Your First GROUP BY

SELECT
  cust_id,
  COUNT(*) AS order_count
FROM orders
GROUP BY cust_id;

Instead of one count for everyone, you get one count per department. The database groups all HR employees, counts them, and then does the same for Admin, Account, and so on.

The Golden Rule

Here’s something many beginners find confusing: when you use GROUP BY, every column in your SELECT must either be in your GROUP BY clause or inside an aggregate function. No exceptions.

-- This works: department is in GROUP BY
SELECT
  department,
  AVG(salary)
FROM techcorp_workforce
GROUP BY department;

-- This fails: first_name isn’t grouped or aggregated
SELECT
  department,
  first_name,
  AVG(salary)
FROM techcorp_workforce
GROUP BY department;

Why? Think about it: if you’re grouping by department, and HR has four employees, which first name should SQL show? It can’t pick one, so it refuses to run.

Sorting Your Groups

GROUP BY results come back in no particular order. Usually, you want them sorted — either alphabetically or by the aggregate value:

-- Sort by department name
SELECT
  department,
  SUM(salary) AS dept_payroll
FROM techcorp_workforce
GROUP BY department
ORDER BY department;

MacBookPro User Event Count

Group by event name and use COUNT.

Artist Appearance Count

Group by artist and count how many times they appear.

Top Ranked Songs

You’ll need GROUP BY with a WHERE filter here.

Key Takeaways

• GROUP BY splits your data into groups before aggregating
• Every SELECT column must be in GROUP BY or an aggregate function
• You can group by multiple columns for finer breakdowns
• Use ORDER BY to sort your results (it comes after GROUP BY)

What’s Next

GROUP BY creates your groups, but what if you only want some of them? Say, only departments with more than five employees, or only months with revenue over $100K. You can’t use WHERE for this — WHERE filters rows before grouping happens. You need something that filters after. That’s HAVING, and it’s next.