Here's a preview from my zine, Become a SELECT Star!! If you want to see more comics like this, sign up for my saturday comics newsletter or browse more comics!
get the zine!
read the transcript!
GROUP BY combines multiple rows into one row. Here’s how it works for this table & query:
SELECT item, (COUNT(*), MAX(price)
FROM sales
GROUP BY item
(COUNT(*), MAX(price) aggregates
sales:
| item | price |
|---|---|
| catnip | 5 |
| laser | 8 |
| tuna | 4 |
| tuna | 3 |
query output:
| item | count | price |
|---|---|---|
| catnip | 1 | 5 |
| laser | 1 | 8 |
| tuna | 2 | 4 |
- Split the table into groups for each value that you grouped by:
item=‘catnip’
| item | price |
|---|---|
| catnip | 5 |
item=‘laser’
| item | price |
|---|---|
| laser | 8 |
item=‘tuna’
| item | price |
|---|---|
| tuna | 4 |
| tuna | 3 |
- Calculate the aggregates from the query for each group:
| item | price |
|---|---|
| catnip | 5 |
COUNT(*)=1
MAX(price)=5
| item | price |
|---|---|
| laser | 8 |
COUNT(*)=1
MAX(price)=8
| item | price |
|---|---|
| tuna | 4 |
| tuna | 3 |
COUNT(*)=2
MAX(price)=4
- Create a result set with 1 row for each group
| item | count | price |
|---|---|---|
| catnip | 1 | 5 |
| laser | 1 | 8 |
| tuna | 2 | 4 |