Group by Month in SQL: Monthly Totals From Daily Rows
Turn one row per order into one row per month. Cut each date down to year and month, group by it, count orders and add up sales, then find the best month, on a sample online shop database.
Step by step
Monthly totals
- Daily rows to months. The orders table has one row per order, but a monthly report needs one row per month. The fix is to cut each date down to its year and month, then group by it.
- The daily rows. Start with the raw data. SELECT order_date, order_total FROM orders; lists the date and total of every order. It returns 141 rows, one per order. That is far too detailed for a monthly report.
SELECT order_date, order_total FROM orders;
Year and month
- Cut the date. Replace the query with strftime('%Y-%m', order_date) AS month. %Y keeps the 4-digit year and %m keeps the 2-digit month, so each date becomes a label in the form YYYY-MM. AS month names the new column. Run it and you still get 141 rows, but each date is now just a year and a month. This is SQLite. PostgreSQL uses date_trunc and SQL Server uses FORMAT for the same step.
SELECT strftime('%Y-%m', order_date) AS month FROM orders;
Group by month
- Group and sum. Put a comma after AS month, add SUM(order_total) AS sales, and end with GROUP BY month. Every order in the same month falls into one group, and SUM adds up its totals. The result drops to 12 rows, one for each month, each with its total sales.
AS month, SUM(order_total) AS sales FROM orders GROUP BY month; - Count orders. Add COUNT(*) AS orders above the SUM line. COUNT(*) counts the rows in each group, so the report shows how many orders made up each total. Still 12 months, now with an orders column beside each sales total.
COUNT(*) AS orders, SUM(order_total) AS sales
Clean the numbers
- Skip cancelled. Cancelled orders aren't real sales. Add WHERE status <> 'Cancelled' under FROM orders. WHERE removes those rows before the grouping happens. Still 12 months, but some totals are now lower. The difference was the cancelled orders.
FROM orders WHERE status <> 'Cancelled'
Best month
- Best month first. Last, change the final line to GROUP BY month ORDER BY sales DESC; DESC sorts the months from the biggest sales total to the smallest. January comes first with about $1,570 in sales, and February is second. The whole monthly sales report is 6 lines of SQL.
GROUP BY month ORDER BY sales DESC;
Recap
- Group by month. In short: cut each date to year and month, group by it, then count and sum. Keep the year in the label. If you group by the month alone, January of 2 different years lands in one group.
Common mistakes
- Grouping by the month number only. strftime('%m', order_date) gives 01 for every January, so once your data covers more than 1 year, those months get added together. Use '%Y-%m' so each year's months stay separate.
- Typing %M instead of %m. In SQLite, %M is minutes, not month, so the groups come out wrong. The month is a small %m, and the 4-digit year is a capital %Y.
- Dates stored as text like 21/10/2024. SQLite's date functions only understand formats like YYYY-MM-DD. Any other format makes strftime return NULL, and all those rows land in one NULL group. Convert the dates to YYYY-MM-DD first.
- Using the alias in GROUP BY on SQL Server. SQLite, MySQL and PostgreSQL accept GROUP BY month, but SQL Server does not allow a column alias there. Repeat the full expression in GROUP BY, for example GROUP BY FORMAT(order_date, 'yyyy-MM').
When a business analyst uses this
Before signing off a new sales dashboard, a BA can run this query on the source orders table and check each month's total and order count against the dashboard. A mismatch, such as cancelled orders still being counted, becomes a defect to log before go-live.
Questions people ask
How do you group by month in SQL Server?
Use FORMAT(order_date, 'yyyy-MM'), or YEAR(order_date) and MONTH(order_date), in both SELECT and GROUP BY. SQL Server doesn't accept the column alias in GROUP BY, so you have to repeat the expression.
How do you group by month in PostgreSQL?
Use date_trunc('month', order_date), which moves every date to the first day of its month, and group by that. If you want a YYYY-MM text label, use to_char(order_date, 'YYYY-MM') instead.
Why group by year and month, not just month?
If you group by the month number alone, January 2024 and January 2025 are added into one row. Keeping the year in the label, like '%Y-%m' in SQLite, gives each month of each year its own row.
