Group Query Results by Month and Year in SQL

1. Introduction

In this tutorial, we’ll learn to group results by month and year in SQL, a common practice for gaining insights into long-term trends.

We’ll use the Registration table from our University schema. In particular, we’ll write queries to retrieve the number of registrations per year and month from the database.

We tested the queries with PostgreSQL 16, MySQL 8, and SQL Server 2022, but they should also work in other versions of these databases.

2. PostgreSQL

PostgreSQL offers several ways of grouping results by month and year.

2.1. Using EXTRACT

We can use the EXTRACT function to extract the year and month from a date or timestamp:

SELECT EXTRACT(YEAR FROM reg_datetime) AS year,
       EXTRACT(MONTH FROM reg_datetime) AS month,
       COUNT(*) AS no_of_registrations
FROM registration 
GROUP BY EXTRACT(YEAR FROM reg_datetime), EXTRACT(MONTH FROM reg_datetime) 
ORDER BY year, month;

When we run this query, we get:

+------+-------+---------------------+
| year | month | no_of_registrations |
+------+-------+---------------------+
| 2022 |     1 |                  38 |
| 2022 |     8 |                  43 |
| 2023 |     1 |                  36 |
| 2023 |     8 |                  30 |
+------+-------+---------------------+

Since we are using the GROUP BY clause, we should ensure that the same columns are specified in both the SELECT and GROUP BY clauses.

2.2. Using DATE_TRUNC

Another way to achieve this is by using the DATE_TRUNC function:

SELECT DATE_TRUNC('month', reg_datetime) AS month_start,
       COUNT(*) AS total_registrations
FROM registration 
GROUP BY month_start
ORDER BY month_start;

The DATE_TRUNC function in PostgreSQL takes two arguments:

1. the period to truncate to, and
2. the timestamp value

When truncating to a period like month, all smaller units, such as day, hour, and minute, are reset to the beginning of the specified period.

In our query, the GROUP BY clause organizes the results by year and month because the truncation adjusts the timestamp to the first day of each month, discarding more granular time details.

When we run the query, we get the following results:

+---------------------+---------------------+
|     month_start     | total_registrations |
+---------------------+---------------------+
| 2022-01-01 00:00:00 |                  38 |
| 2022-08-01 00:00:00 |                  43 |
| 2023-01-01 00:00:00 |                  36 |
| 2023-08-01 00:00:00 |                  30 |
+---------------------+---------------------+

It is important to note that the output of the DATE_TRUNC function is always a timestamp data type.

2.3. Using TO_CHAR

Another method for grouping results by year and month is to use the TO_CHAR function, which formats the timestamp into a desired string format.

By formatting the timestamp as a combination of year and month, we can group the results accordingly:

SELECT TO_CHAR(reg_datetime, 'YYYY-MM') AS year_month,
       COUNT(*) AS total_registrations
FROM registration
GROUP BY year_month
ORDER BY year_month;

In this example, the timestamp is formatted into YYYY-MM, and the GROUP BY clause is applied to this formatted value to group the results by year and month.

3. MySQL

In this section, we’ll look at how to group the results by year and month in MySQL.

3.1. Using DATE_FORMAT

We can use the function DATE_FORMAT to get the year and month from a timestamp and the GROUP BY clause to group the results:

SELECT DATE_FORMAT(reg_datetime, '%Y-%m') AS month_year,
       COUNT(*) AS total_registrations
FROM Registration
GROUP BY month_year
ORDER BY month_year;

The function DATE_FORMAT takes two parameters:

1. the timestamp, and
2. the required format

More details about the different formats are available in the official MySQL documentation.

3.2. Using YEAR and MONTH Functions

MySQL provides functions YEAR and MONTH to extract the year and month part of a timestamp. We can use this to apply grouping on the rows:

SELECT YEAR(reg_datetime) AS year_part, 
       MONTH(reg_datetime) AS month_part, 
       COUNT(*) AS total_registrations
FROM Registration
GROUP BY year_part, month_part
ORDER BY year_part, month_part;

4. SQL Server

Now, let’s check our options in SQL Server.

4.1. Using YEAR and MONTH

Similar to MySQL, we can use the YEAR and MONTH functions in the query to extract the year and month components and group the results based on those values:

SELECT YEAR(reg_datetime) AS year_part, 
       MONTH(reg_datetime) AS month_part, 
       COUNT(*) AS total_registrations
FROM Registration
GROUP BY YEAR(reg_datetime), MONTH(reg_datetime)
ORDER BY year_part, month_part;

Unlike MySQL, in SQL Server, we need to repeat the YEAR and MONTH functions in the GROUP BY clause because SQL Server processes the GROUP BY clause before SELECT.

4.2. Using DATEPART

An alternative method to group rows by year and month is the DATEPART function:

SELECT DATEPART(YEAR, reg_datetime) AS Year, 
       DATEPART(MONTH, reg_datetime) AS Month, 
       COUNT(*) AS total_registrations
FROM Registration
GROUP BY DATEPART(YEAR, reg_datetime), DATEPART(MONTH, reg_datetime)
ORDER BY Year, Month;

The DATEPART function in SQL Server extracts a specific part of a date, such as a year or month.

4.3. Using FORMAT

We can also use the FORMAT function to convert the timestamp values and group the results based on the format:

SELECT FORMAT(reg_datetime, 'yyyy-MM') AS year_month,
       COUNT(*) AS total_registrations
FROM Registration
GROUP BY FORMAT(reg_datetime, 'yyyy-MM')
ORDER BY year_month;

We combine the year and month into a single string of the form “year-month” and group the rows based on this value.

4.4. Using DATETRUNC

Just like in PostgreSQL, SQL Server’s DATETRUNC function can set all the components of a timestamp shorter than the specified period to their starting values:

SELECT DATETRUNC(Month, reg_datetime) AS month_start,
       COUNT(*) AS total_registrations
FROM Registration
GROUP BY DATETRUNC(Month, reg_datetime)
ORDER BY month_start;

Since the ORDER BY is evaluated after the SELECT, we can use the alias value in the ORDER BY clause.

5. Conclusion

In this article, we explored various methods for grouping records by year and month across different databases. Each database system offers several tools and functions for extracting and grouping date components.