In this article, I will show you how to group data by decade, year, quarter, month, week, day, hour, and minute.
To simplify things, the code will group data by time and count the number of rows in each group.
But first, we need to learn about the DATEPART function.
The DATEPART function is a built-in function in SQL Server that returns an integer of a specified datepart of a date.
DATEPART(datepart, date)The datepart argument can be year, month, day, hour, minute, and second.
Meanwhile, the date argument is the date you wish to extract the specified datepart from.
For example:
SELECT
DATEPART(day, date_column) AS [Day]
FROM table_name;The code above will return a Day column listing days (as integer values) extracted for the date_column.
Now that you understand how DATEPART works, let’s get started.
How to group data by decade in SQL
This code groups your data by decade and counts all the rows within each group.
SELECT
LEFT(YEAR(date_column), 3) + '0' AS [Decade]
, COUNT(*) AS [Count]
FROM table_name
GROUP BY LEFT(YEAR(date_column), 3) + '0';
How to group data by year in SQL
This code groups your data by year and counts all the code within each group.
SELECT
YEAR(date_column) AS [Year]
, COUNT(*) AS [Count]
FROM table_name
GROUP BY YEAR(date_column);
How to group data by quarter in SQL
This code groups your data by quarter and counts all the code within each group.
SELECT
DATEPART(quarter, date_column) AS [Quarter],
COUNT(*) AS [Count]
FROM table_name
GROUP BY DATEPART(quarter, date_column);How to group data by month in SQL
This code groups your data by month and counts all the code within each group.
SELECT
MONTH(date_column) AS [Month]
, COUNT(*) AS [Count]
FROM table_name
GROUP BY YEAR(date_column), MONTH(date_column);
How to group data by week in SQL
This code groups your data by week and counts all the code within each group.
SELECT YEAR(date_column) AS [Year]
, DATEPART(week, date_column) AS [Week]
, COUNT(*) AS [Count]
FROM table_name
GROUP BY YEAR(date_column), DATEPART(week, date_column);How to group data by day in SQL
This code groups your data by day and counts all the code within each group.
SELECT CAST(date_column AS DATE) AS [Day]
, COUNT(*) AS [Count]
FROM table_name
GROUP BY CAST(date_column AS DATE);How to group data by hour in SQL
This code groups your data by hour and counts all the code within each group.
SELECT DATEPART(hour, date_column) AS hour, COUNT(*) AS count
FROM table_name
GROUP BY DATEPART(hour, date_column);How to group data by minute in SQL
This code groups your data by minute and counts all the code within each group.
SELECT DATEPART(minute, date_column) AS minute, COUNT(*) AS count
FROM table_name
GROUP BY DATEPART(minute, date_column);
How to group data by second in SQL
This code groups your data by second and counts all the code within each group.
SELECT DATEPART(second, date_column) AS [Second],
COUNT(*) AS [Count]
FROM table_name
GROUP BY DATEPART(second, date_column);
You can also group by multiple time periods. For instance, you can group by year, month, day, and hour to get more specific as shown in the code below.
SELECT
YEAR(date_column) AS [Year],
MONTH(date_column) AS [Month],
DAY(date_column) AS [Day],
DATEPART(hour, date_column) AS [Hour],
COUNT(*) AS [Count]
FROM table_name
GROUP BY YEAR(date_column),
MONTH(date_column),
DAY(date_column),
DATEPART(hour, date_column);I hope this was helpful.