Schema and sample data
| order_id | order_date | amount |
|---|---|---|
| 1 | 2024-01-05 | 120.00 |
| 2 | 2024-01-12 | 80.00 |
| 3 | 2024-01-28 | 45.50 |
| 4 | 2024-02-14 | 210.00 |
| 5 | 2024-03-03 | 150.00 |
| 6 | 2024-03-19 | 95.00 |
| 7 | 2024-04-08 | 300.00 |
| 8 | 2024-04-22 | 60.00 |
Show setup SQL (copy to run)
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
order_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL
);
INSERT INTO Orders (order_id, order_date, amount) VALUES
(1, '2024-01-05', 120.00),
(2, '2024-01-12', 80.00),
(3, '2024-01-28', 45.50),
(4, '2024-02-14', 210.00),
(5, '2024-03-03', 150.00),
(6, '2024-03-19', 95.00),
(7, '2024-04-08', 300.00),
(8, '2024-04-22', 60.00);From the Orders table, return one row per calendar month showing the year and month as a single YYYY-MM value and the number of orders placed in that month. Alias the columns order_month and order_count, and sort the result chronologically from the earliest month to the latest.
Expected result
| order_month | order_count |
|---|---|
| 2024-01 | 3 |
| 2024-02 | 1 |
| 2024-03 | 2 |
| 2024-04 | 2 |
Show hint
You need to collapse each date down to its month so that every order in the same month lands in the same group. A tidy way is FORMAT(order_date, 'yyyy-MM'), then GROUP BY that same expression and COUNT(*) the rows in each group.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT FORMAT(order_date, 'yyyy-MM') AS order_month,
COUNT(*) AS order_count
FROM Orders
GROUP BY FORMAT(order_date, 'yyyy-MM')
ORDER BY order_month;FORMAT(order_date, 'yyyy-MM') turns each date into a text label like 2024-01, throwing away the day so that all orders in the same month share one label. Grouping on that same expression collapses the eight rows into four buckets, and COUNT(*) counts the rows in each bucket: January has 3, February has 1, and March and April have 2 each.
The GROUP BY must repeat the full FORMAT(...) expression, not the order_month alias. SQL Server processes GROUP BY before the SELECT list is evaluated, so the alias does not yet exist there. The ORDER BY, however, runs last and can use the alias. Because the YYYY-MM text sorts the same way as the real calendar order, sorting by order_month gives a correct chronological result.
An alternative that avoids string formatting is to group by YEAR(order_date) and MONTH(order_date) as two separate columns. That keeps the parts numeric (handy if you later need to filter or join on them), but you then sort by year and month rather than by a single label. If you need the span between two dates instead of a bucket count, see DATEDIFF.