Sports are very popular in the US, so I want to find if the quantity of books checked out is in accordance with the popularity and game time of several popular sports. So my question is: How has check out quantity of books about football, baseball, basketball and hockey varied in accordance with the game time and popularity of the four big leagues: NFL, MLB, NBA and NHL?

Basically, I want to get the check out sum of the books about these four sports in every month in each year. As we know, these four sports all have a specific period of time and I make a intuitive guess that during the game time people tend to check out books more often than the time there are no games. Also, books about the game which is more popular should be checked out more times than those are less popular.

select month(cout) as 'Month', year(cout) as 'Year', sum(case when (title like '%basketball%') then 1 else 0 end) as 'Basketball', sum(case when (title like '%football%') then 1 else 0 end) as 'Football', sum(case when (title like '%baseball%') then 1 else 0 end) as 'Baseball', sum(case when (title like '%hockey%') then 1 else 0 end) as 'Hockey' from inraw where (title like '%basketball%' or title like '%football%' or title like '%baseball%' or title like '%hockey%') and year(cout) >= 2008 group by year(cout), month(cout) order by year(cout), month(cout) asc;

We give the time when these fours sports have games. NFL: Aug. ­ Feb. MLB: Apr. ­ Nov. NBA: Nov. ­ Jun. NHL: Oct. ­ Jun. Popularity: football > baseball > basketball > hockey. From the result we can see that people tend to check out hockey books least, this is reasonable according to the popularity. In terms of football and basketball, people tend to check out basketball books from February to the time NBA ends, while from Augest to January people check out books about football more than books about basketball to some degree, which is in accordance with the game time of these two sports and their popularity. In terms of baseball, it’s reasonable people check out books most during Apr, May, June and July in the whole year when baseball games have just started after a half year pause. However, it’s strange people check out baseball books most, I think maybe Seattle people like their local baseball team more and their library has more books about baseball than football.

Source Code