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

Grouping by Two Columns

Piles inside piles.

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

Helen wants to compare years. In each year, how many orders were delivered, shipped, cancelled, or returned? That means grouping by the year, and by the status, at the same time.

Grouping by Two Columns
From the video: Grouping by Two Columns
  • Compare the two years
  • By year, and by status
  • Two columns at once

Two grouping columns

List both after group by, with a comma. Now there's one pile for every combination of year and status. Run it. In twenty twenty five, one hundred orders were cancelled, compared with forty the year before. Cancellations grew faster than orders. That's worth investigating.

query.sqlsql
SELECT strftime('%Y', order_date) AS year,
       status,
       COUNT(*) AS orders
FROM orders
GROUP BY year, status
ORDER BY year, status;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ year โ”‚  status   โ”‚ orders โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 2024 โ”‚ cancelled โ”‚ 40     โ”‚
โ”‚ 2024 โ”‚ delivered โ”‚ 442    โ”‚
โ”‚ 2024 โ”‚ returned  โ”‚ 22     โ”‚
โ”‚ 2024 โ”‚ shipped   โ”‚ 55     โ”‚
โ”‚ 2025 โ”‚ cancelled โ”‚ 100    โ”‚
โ”‚ 2025 โ”‚ delivered โ”‚ 823    โ”‚
โ”‚ 2025 โ”‚ returned  โ”‚ 41     โ”‚
โ”‚ 2025 โ”‚ shipped   โ”‚ 75     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

The golden rule

Here's the golden rule of grouping. Every column in your select must be either in the group by, or inside an aggregate function. Year and status are in the group by. Count is an aggregate. So this query follows the rule.

In GROUP BYyear, status
Inside an aggregateCOUNT(*)
Anything elsebreaks the rule
Rulegroup it, or aggregate it

Summary

You can group by several columns, and you know the golden rule. Next, what happens when you break it.

Key takeaways
  • GROUP BY a, b
  • One group per combination
  • Group it, or aggregate it
Next up: A Grouping Mistake โ€” why the rule matters