Grouping by Date Parts
Group store orders by order month to chart order volume over time.
Overview
Dates are often too granular to group by directly, so you extract a coarser
part — month, year, or both — and group on that. Here we count orders per
YYYY-MM month using DuckDB's strftime to format the date.
The Query
Store (Customers/Orders/Products)
Loading database engine...
Step-by-Step Breakdown
strftime(order_date, '%Y-%m')formats each date as2022-01, collapsing day-level detail into months.GROUP BY month— one row per calendar month present in the data.COUNT(*)andSUM(quantity)summarize activity per month.
Date functions vary by engine
DuckDB uses strftime(date, format). Other engines use DATE_TRUNC('month', date) or EXTRACT. The concept — group on a truncated date — is universal.
Variations
Group movies by release decade:
Movies (Movies/Actors/Ratings)
Loading database engine...
Common Mistakes
- Grouping by the raw date.
GROUP BY order_dategives one row per day, not per month — often not what you want. - Filtering the formatted string in WHERE incorrectly. Filter the original
column (
WHERE order_date >= DATE '2022-03-01'), not the aliased expression, for best compatibility. - Mismatched SELECT/GROUP BY. The exact expression used in
GROUP BYmust appear (or be an aggregate) inSELECT.