GROUP BY
Collapses rows that share the same values into one row per group, so aggregates such as COUNT, SUM or AVG are computed for each group.
Formulaone output row per distinct combination of the GROUP BY columns
How we work it through
- 1
Decide the grain of the answer: per day, per seller, per week.
- 2
Group by exactly that — a date, not a timestamp.
- 3
Count the output rows; they should match what you expect.
On the practice dataset
The query we run on Meridian, Atterna’s practice marketplace, and what it returns.
SELECT CAST(created_at AS DATE) AS day, COUNT(*)
FROM meridian.orders
WHERE status = 'paid'
GROUP BY 1;
-- GROUP BY created_at gives 9,017 groupsResult23 rows, one per day; grouping by the timestamp returns 9,017
The common trap
Grouping by a column with more detail than intended (a timestamp instead of a date), which returns one row per event instead of per day.
When an AI answers
“Here are orders per day (9,017 rows).”
- What it didn’t know
- It grouped by the full timestamp and did not notice the row count.
- Ask back
- “Why are there more rows than days? Group by the date.”
Read next
Examples use Meridian, Atterna’s fictional practice marketplace.