Forgly

SQL to group by month

Group rows by month in SQL — truncate a date or timestamp to year-month to count or sum records per calendar month.

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

To group by month in SQL, truncate each date to its year-month and GROUP BY that. Copy-paste examples below for PostgreSQL (DATE_TRUNC), MySQL (DATE_FORMAT), SQL Server (FORMAT), and SQLite (strftime) — each counts rows per calendar month.

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?

To group by month in SQL, truncate each date to its year-month and GROUP BY that. Copy-paste examples below for PostgreSQL (DATE_TRUNC), MySQL (DATE_FORMAT), SQL Server (FORMAT), and SQLite (strftime) — each counts rows per calendar month. 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.