How to Group by Month in T-SQL
Mục Lục
Problem:
You’d like to group records by month in a SQL Server database.
Example:
Our database has a table named furniture
with data in the columns id
, name
, and production_timestamp
.
idnameproduction_timestamp
1double bed2022-02-01 11:45:23
2coffee table2022-02-01 11:46:13
3chest of drawers2022-01-22 17:22:05
Solution:
You can use two DATEPART()
functions to group records in a table by month and year.
Here’s the query you would write:
SELECT DATEPART(YEAR, production_timestamp) AS year, DATEPART(MONTH, production_timestamp) AS month, COUNT(id) AS count FROM furniture GROUP BY DATEPART(MONTH, production_timestamp), DATEPART(YEAR, production_timestamp);
Here’s the result of the query:
yearmonthcount
202211
202222
Discussion:
Grouping records by month is a very common operation in SQL Server. In our example, the number of products is totaled for each month of the year. You usually want to take into account both the year and the month of the date, i.e. you want to group events in January 2022 separately from events in January 2021.
To group data by month in SQL Server, use the DATEPART()
function. It extracts the given part (year, month, etc.) from the date. We use the function twice: once with the MONTH
argument and once with the YEAR
argument. This extracts the month and year (respectively) from the production timestamp. We combine the two calls to DATEPART()
in the GROUP BY
clause to group furniture by the month and year when they were produced.
We use the same two calls to DATEPART()
in the SELECT
clause to display the label for the year and month.