Sql Grouping by on a range of year

Question

This is my table Orders

If I want to have the count of orders in each exact year, I simply can write this query

Select COUNT(*) CountOfOrders, Year(OrderDate) Year  
From Orders 
Group by YEAR(OrderDate)

so having this resultset

But what should Id do if I need to have this result set?

CoundOfOrder       FromYear_toYear
     5            2005-2010
     4            2010-2015

Answer 1

You can try this query. This will help you in the simplest way.

SELECT  COUNT(*),(CONVERT(VARCHAR,MIN(year(orderdate)))+'-' + CONVERT(VARCHAR,MAX(year(orderdate)))) AS yearRange
FROM orders
GROUP BY FLOOR(Year(OrderDate) /5)

You Can refer the link SQL_Fiddle_Link that uses your example to form this query.

Answer 2

Select COUNT(*) CountOfOrders, 
CASE WHEN Year(OrderDate) BETWEEN 2005 AND 2009 THEN '2005-2009'
     WHEN Year(OrderDate) BETWEEN 2010 AND 2015 THEN '2010-2015'
END AS Year  
From Orders 
Group by 
CASE WHEN Year(OrderDate) BETWEEN 2005 AND 2009 THEN '2005-2009'
     WHEN Year(OrderDate) BETWEEN 2010 AND 2015 THEN '2010-2015'
END

Note that I adjusted your date ranges, or you wouldn't get meaningful data, counting some things in 2 categories.