GROUP BY and Aggregation

Aggregation turns many rows into summary rows. Instead of returning every order, you can count orders by status:

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC;

GROUP BY decides the grain of the result. If you group by status, there is one row per status. If you group by status, user_id, there is one row per status and user combination.

WHERE vs HAVING

Use WHERE before grouping to filter raw rows:

WHERE created_at >= '2026-01-01'

Use HAVING after grouping to filter aggregate results:

HAVING COUNT(*) > 10

If a report looks wrong, check whether the filter belongs before or after aggregation.

Date buckets are dialect-specific

Reporting queries often group by day, week, or month. Each database has different date functions. PostgreSQL might use date_trunc, while MySQL and SQLite use different functions.

This is one reason the SQL Formatter exposes dialect choices. Formatting can handle common structures better when it knows the language family.

Key takeaway

Before reading aggregate numbers, identify the grain: one row per what? Most aggregation bugs come from grouping too broadly, grouping too narrowly, or filtering at the wrong stage.

Try formatting an aggregate query with the SQL Formatter and mark which columns define the result grain.