レッスン 4
GROUP BY and Aggregation 日本語ガイド
日本語の sql group by and aggregation ガイド: Summarize rows with COUNT, SUM, GROUP BY, HAVING, and date buckets.
このコンテンツはまだ日本語で用意されていません。ローカライズが完了するまで English 版を表示しています。
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.