Data · SQL
How GROUP BY works in SQL
8 orders from 4 customers: how many rows does GROUP BY customer return?
And GROUP BY city, if all 8 ship to Boston?
From one group per customer to the column that breaks the query, in five steps:
How many rows does a GROUP BY return?
With GROUP BY customer, Emma’s 7 orders go into a single group, and that group gives a single row of the result.
Let’s take six orders.
SELECT customer FROM orders GROUP BY customer
| id | customer | city | amount |
|---|---|---|---|
| 101 | Emma | Boston | $40 |
| 102 | Noah | Denver | $25 |
| 103 | Emma | Boston | $60 |
| 104 | Mia | Austin | $15 |
| 105 | Noah | Boston | $80 |
| 106 | Emma | Denver | $35 |
| customer |
|---|
| Emma |
| Noah |
| Mia |
Which orders go into Emma’s group? Tap them in the table.
Look for Emma in the
customercolumn, from the first row to the last.
SELECT customer FROM orders GROUP BY customer
orders id customer city amount 101 Noah Boston $40 102 Mia Denver $25 103 Noah Denver $60 104 Liam Boston $15 105 Mia Boston $80 SELECT city FROM orders GROUP BY city
orders id customer city amount 101 Emma Boston $40 102 Noah Denver $25 103 Mia Boston $60 104 Emma Denver $15 105 Liam Boston $80 106 Noah Boston $35 107 Mia Denver $50
What do COUNT and SUM return with GROUP BY?
SUM(amount) turns a group into a single number: a group holding $30 and $45 gives one row that shows $75.
Let’s take six other orders, grouped by customer.
SELECT customer, COUNT(*), SUM(amount) FROM orders GROUP BY customer
| id | customer | city | amount |
|---|---|---|---|
| 101 | Noah | Boston | $15 |
| 102 | Emma | Denver | $80 |
| 103 | Mia | Boston | $35 |
| 104 | Emma | Boston | $50 |
| 105 | Noah | Austin | $20 |
| 106 | Emma | Denver | $70 |
| customer | COUNT(*) | SUM(amount) |
|---|---|---|
| Noah | 2 | $35 |
| Emma | 3 | $200 |
| Mia | 1 | $35 |
SELECT customer, SUM(amount) FROM orders GROUP BY customer
orders id customer city amount 101 Mia Boston $40 102 Liam Denver $25 103 Mia Denver $60 104 Liam Boston $15 105 Emma Boston $80 SELECT city, SUM(amount) FROM orders GROUP BY city
orders id customer city amount 101 Emma Boston $50 102 Noah Denver $20 103 Emma Boston $70 104 Mia Austin $45 105 Noah Boston $40 106 Liam Denver $25
How do you GROUP BY two columns?
With GROUP BY customer, city, two rows go into the same group only if they have the same customer and the same city: Emma in Miami and Emma in Seattle make two groups.
Let’s take six orders, shipped to two cities.
SELECT customer, city, COUNT(*) FROM orders GROUP BY customer, city
| id | customer | city | amount |
|---|---|---|---|
| 101 | Emma | Boston | $40 |
| 102 | Noah | Boston | $25 |
| 103 | Emma | Denver | $60 |
| 104 | Emma | Boston | $15 |
| 105 | Noah | Boston | $80 |
| 106 | Mia | Denver | $35 |
| customer | city | COUNT(*) |
|---|---|---|
| Emma | Boston | 2 |
| Noah | Boston | 2 |
| Emma | Denver | 1 |
| Mia | Denver | 1 |
Which orders go into the same group as order 101?
Look for the rows that have both the customer and the city of order 101.
SELECT customer, city, COUNT(*) FROM orders GROUP BY customer, city
orders id customer city amount 101 Mia Austin $40 102 Liam Austin $25 103 Mia Boston $60 104 Mia Austin $15 105 Liam Boston $80 106 Liam Austin $35 107 Mia Austin $50 108 Noah Boston $20 SELECT customer, city, COUNT(*) FROM orders GROUP BY customer, city
orders id customer city amount 101 Noah Denver $40 102 Liam Boston $25 103 Noah Boston $60 104 Noah Denver $15 105 Liam Boston $80 106 Noah Denver $35
Why must a column be in the GROUP BY?
Every column in the SELECT needs one value per group: with GROUP BY customer, if Emma’s group holds $30 and $55, there is no single amount to show for her.
Let’s take this query.
SELECT customer, amount FROM orders GROUP BY customer
| id | customer | city | amount |
|---|---|---|---|
| 101 | Emma | Boston | $40 |
| 102 | Liam | Denver | $25 |
| 103 | Emma | Austin | $60 |
| 104 | Mia | Boston | $15 |
| 105 | Liam | Denver | $80 |
ERROR: column "orders.amount" must appear in the GROUP BY clause or be used in an aggregate function
Which amount should Emma’s row show?
Look at the amounts in Emma’s group.
Which fix keeps one row per customer?
SELECT city, customer, SUM(amount) FROM orders GROUP BY city
Which column of the
SELECTbreaks the query?SELECT customer, city, amount, COUNT(*) FROM orders GROUP BY customer, city
Which column of the
SELECTbreaks the query?
How do you read a GROUP BY quickly?
Read the GROUP BY line first, since it says what makes a group. Then check that every other column in the SELECT is inside a function.
SELECT city FROM orders GROUP BY city
orders id customer city amount 101 Noah Boston $40 102 Emma Austin $25 103 Mia Boston $60 104 Noah Austin $15 105 Emma Boston $80 106 Liam Austin $35 SELECT customer, SUM(amount) FROM orders GROUP BY customer
orders id customer city amount 101 Liam Boston $60 102 Emma Denver $15 103 Liam Denver $80 104 Noah Boston $35 105 Liam Austin $50 106 Emma Boston $20 SELECT customer, SUM(amount), city FROM orders GROUP BY customer
Which column of the
SELECTbreaks the query?SELECT customer, city, COUNT(*) FROM orders GROUP BY customer, city
orders id customer city amount 101 Emma Denver $40 102 Mia Denver $25 103 Emma Boston $60 104 Emma Denver $15 105 Mia Denver $80 106 Mia Boston $35 107 Emma Denver $50