GROUP BY
One answer per group.
Helen asks, how many customers do I have in each country? With what you know, you could run one query per country. Where country equals UK, count. Then the USA. Then Canada. Six queries. And if she had fifty countries, fifty queries. There must be a better way.

- Customers in each country
- One query per country?
- There's a better way
The slow way
Here's the slow way, for just one country. Run it. One hundred and seventeen customers in the UK. It works, but we'd have to repeat it for every country.
SELECT COUNT(*)
FROM customers
WHERE country = 'UK';โโโโโโโโโโโโ โ COUNT(*) โ โโโโโโโโโโโโค โ 117 โ โโโโโโโโโโโโ
How GROUP BY works
Group by does all of them at once. Imagine sorting all the customers into piles, one pile per country. Then the count runs on each pile separately. You get one row back for each pile.
Every country at once
Group by country goes after from, and before order by. We select the country, so we know which pile each number belongs to, and the count. Run it. Every country, with its number of customers, in one query. The USA is the biggest market.
SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY customers DESC;โโโโโโโโโโโโโฌโโโโโโโโโโโโ โ country โ customers โ โโโโโโโโโโโโโผโโโโโโโโโโโโค โ USA โ 126 โ โ UK โ 117 โ โ Australia โ 86 โ โ Canada โ 77 โ โ Ireland โ 52 โ โ Germany โ 42 โ โโโโโโโโโโโโโดโโโโโโโโโโโโ
Summary
Group by splits rows into piles, and the aggregate runs on each pile. It's one of the most important ideas in SQL. Let's use it some more.
Key takeaways- GROUP BY makes piles
- The aggregate runs per pile
- One row per group