How to Group by Year in T-SQL

Problem

You want to group your data by year in SQL Server database.

Example

One of the columns in your data is transaction_date. It contains a date. You would like to group all your data by year and calculate the total money earned each year.

The data table looks like this:

Solution 1: Displaying the year and the money earned

SELECT
  YEAR(transaction_date) AS year,
  SUM(money) AS money_earned
FROM data
GROUP BY YEAR(transaction_date);

The result is:

Solution 2: Displaying the complete date, the year, and the money earned in the corresponding year

SELECT
  transaction_date AS transaction_date,
  YEAR(transaction_date) AS year,
  SUM(money) OVER(PARTITION BY YEAR(transaction_date)) AS money_earned
FROM data;

The result is:

Discussion

In this example it's assumed that you don't have the year column. You have the column with complete dates and would like to retrieve the year from it.

To retrieve a year from the date in SQL Server, you can use the YEAR() function. The argument of this function should be a date – here, the transaction_date column.

If you'd like to display the year and the total money earned in this year, you can use a GROUP BY. The first selected column is the year extracted from the date. The second column is the aggregate function SUM(money). At the end of the query you need a GROUP BY YEAR(transaction_date). Hence Solution 1.

If you'd like to display more columns, you use a window function (Solution 2). After SUM(money) you write the OVER() clause and, since you'd like to group by each year, use PARTITION BY YEAR(transaction_date) inside it. Note that you don't yet have the year column when counting the sum, so PARTITION BY year won't work. You can read more about the window functions here.