Ashutosh Ashutosh -4 years ago 201
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


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 Source

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`)


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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download