AI Hints
Homeโ€บSQL Tutorialโ€บGrouping Rows
SQL ยท Lesson 35 of 126

GROUP BY

One answer per group.

โฑ 2 min readโ–ถ Video at 49:46๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

GROUP BY
From the video: GROUP BY
  • 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.

query.sqlsql
SELECT COUNT(*)
FROM customers
WHERE country = 'UK';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

query.sqlsql
SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY customers DESC;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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
Next up: More Grouping โ€” several numbers per group