May 3, 2012
MySql: Querying data by month
I realize that it is a common mistake to query monthly data using GROUP BY MONTH(date). It might seem to work but if your date data span more than a single year, you would actually group new and old year data of the same month together.
To avoid such a circumstances, you could instead use YEAR_MONTH as it would return a unique month signature for the different year. Take a look at the query snippet below.
By Month Query =================
SELECT COUNT(*) FROM table GROUP BY EXTRACT(YEAR_MONTH FROM timestamp)
