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=‘laser’
item=‘tuna’
- Calculate the aggregates from the query for each group:
COUNT(*)=1
MAX(price)=5
COUNT(*)=1
MAX(price)=8
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 |
