Forgly

SQL to group by month

Summarize rows per calendar month from a date or timestamp column.

PostgreSQL
SELECT DATE_TRUNC('month', created_at) AS month,
       COUNT(*) AS orders
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
MySQL
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
       COUNT(*) AS orders
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;
SQL Server
SELECT FORMAT(created_at, 'yyyy-MM') AS month,
       COUNT(*) AS orders
FROM orders
GROUP BY FORMAT(created_at, 'yyyy-MM')
ORDER BY month;
SQLite
SELECT strftime('%Y-%m', created_at) AS month,
       COUNT(*) AS orders
FROM orders
GROUP BY strftime('%Y-%m', created_at)
ORDER BY month;

How to group records by month

Grouping by month means truncating each timestamp to its month and aggregating. The function differs by dialect.

How it works

  • Truncating or formatting the timestamp to year-month gives one value per calendar month.
  • Grouping by that expression buckets every row into its month.
  • Including the year (yyyy-MM) keeps the same month across different years separate.

Related SQL queries

Frequently asked questions

How do I group records by month in SQL?

Grouping by month means truncating each timestamp to its month and aggregating. The function differs by dialect. Truncating or formatting the timestamp to year-month gives one value per calendar month. Grouping by that expression buckets every row into its month. Including the year (yyyy-MM) keeps the same month across different years separate.

Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?

Yes — this page lists the query for each dialect, since the syntax can differ between database engines.

Can I generate this query for my own tables?

Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.