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
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]);
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 '[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.