SQLTeam.com | Weblogs | Forums

How to group records by a year?


#1

I have sales to count: how many of them were made last year, a year before that and so on... The problem is that each sale record in date field has a date that looks pretty much like this: 12.11.2013. So, I can't just group them by date, coz then I don't get what I need. Could you help, please


#2

You need to extract the year and group it by that. To extract, if the date stored as a string and is exactly in the format that you have shown, then do this:

SELECT
	REVERSE(LEFT(REVERSE([YourDateColumn]),4)) AS [Year],
	COUNT(*) AS SalesCount
FROM
	YourTable
GROUP BY
	REVERSE(LEFT(REVERSE([YourDateColumn]),4));

Usually dates should not be stored as strings; they should be stored as a DATE or DATETIME (or a similar) data type. If that is the case, then you could use this:

SELECT
	YEAR([YourDateColumn]) AS [Year],
	COUNT(*) AS SalesCount
FROM
	YourTable
GROUP BY
	YEAR([YourDateColumn]);

#3

If the year is always last, and is always all 4 digits, it's safer and easier to do this:

SELECT RIGHT(date_column, 4) AS Year, COUNT(*) AS Sales_Count
FROM table_name
GROUP BY RIGHT(date_column, 4) AS Year

You could even verify that the value "looks like" a year:

WHERE RIGHT(date_column, 4) LIKE '[12][0-9][0-9][0-9]'

Or, if necessary, we could even adjust the code to pick up the last 4 bytes anywhere in the column that match a year pattern.