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.