Group by Month in SQL

Example query

This example assumes: orders(id, order_date date, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT DATE_TRUNC('month', order_date)::date AS month,
       COUNT(*) AS orders,
       SUM(total_amount) AS sales
FROM orders
GROUP BY 1
ORDER BY 1;

How it works

• DATE_TRUNC('month', order_date) rounds every date down to the first day of its month, so all dates in March become 2026-03-01.
• Casting to date with ::date drops the time part from the result.
• GROUP BY 1 groups by the first output column, so you do not repeat the expression.

MySQL, BigQuery, SQL Server and SQLite differences

• MySQL has no DATE_TRUNC: use DATE_FORMAT(order_date, '%Y-%m-01') or DATE_SUB(order_date, INTERVAL DAYOFMONTH(order_date) - 1 DAY).
• BigQuery uses DATE_TRUNC(order_date, MONTH); SQL Server 2022+ has DATETRUNC(month, order_date); SQLite uses strftime('%Y-%m', order_date).

Common mistakes

• Months with no orders are missing from the output; fill them if you chart a continuous line.
• The current month is partial, so compare it with the same days last month, not the whole month.
• If order_date is a timestamp, the month boundary depends on the session time zone; convert it first.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

• Show orders and sales by month for the last 12 months.
• How many new customers signed up each month this year?