# 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:

transaction_datemoney
2022-03-251700
2023-09-12100
2022-07-141200
2022-01-05400
2023-06-082000
2021-03-061500

## 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:

yearmoney_earned
20211500
20232100
20223300

## 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:

transaction_dateyearmoney_earned
2022-03-2520223300
2022-07-1420223300
2022-01-0520223300
2023-09-1220232100
2023-06-0820232100
2021-03-0620211500

## 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.