Ashutosh Ashutosh - 3 months ago 13
MySQL Question

MySQL group by month not working

I think I'm doing something silly as this query is not giving me desired output:

My table is this:

id integer,
current_date date


The entries in table are:

1, 2016-08-24
2, 2016-08-25
3, 2016-08-26
4, 2016-07-21
5, 2016-07-22


Query:

select MONTH(current_date),count(*)
from test
group by MONTH(current_date)


This query is returning me:

8, 5


I was expecting:

7, 2
8, 3


Please help me

Answer

current_date is a MySQL reserved word that returns today's date. To use it as a table column, you need to put it in backticks.

select MONTH(`current_date`), COUNT(*)
from test
GROUP BY MONTH(`current_date`)

DEMO

Or you could rename the column to something that doesn't conflict with a reserved word.