where clause with two months in different years

Question

I want select values from specified seasons of the year grouped by year. Seasons are as follows: December(year before), January, February: Winter, March, April, May: Spring, June, July, August: Summer, September, October, November: Autumn

If i want to get single season grouped by year, my query for Spring, Summer and Autumn looks like this:

select avg(value) from table where month(date) between 3 and 5 group by year(date);

But i don't have idea how to achieve such a result for winter, where, for example, December is in 2017, and January and February in 2018. Grouping by year is very important. Thank You for help!

Answer 1

Use in:

select year(date), avg(value)
from table
where month(date) in (12, 1, 2) 
group by year(date);

If you want the months to be "contiguous" so December, January, and February are in the same year, then add one month to the group by:

select year(date + interval 1 month), avg(value)
from table
where month(date) in (12, 1, 2) 
group by year(date + interval 1 month);