Mysql count month

Question

I have a list of dates from table like this:

2014-05-21
2014-08-20
2014-06-03
2014-06-03
2014-05-24
2014-05-28
2014-05-24
2014-05-27
2014-08-20
2014-05-28
and so on.

I need to count the total grouped by month for each date like this: 05 = 6, 06 = 2, 08 = 2.
I am very noob in mysql, can you guys help me on how I should do this? Thanks.

Yes I just noticed that :| I may just have to include WHERE YEAR(date) = 2014 in the query. Wait, that is wrong I guess. — Shah Erhan
– Shah Erhan, Commented Oct 26, 2014 at 8:35
You could, but that cannot use an index. Better to say " date between '2014-01-01' and '2014-12-31' " — Strawberry
– Strawberry, Commented Oct 26, 2014 at 8:41

M Khalid Junaid · Accepted Answer · 2014-10-26 07:51:56Z

2

You can do so

select month(`date`) `month`,
count(*)
from test 
group by `month`

DEMO

answered Oct 26, 2014 at 7:51

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shah Erhan Over a year ago

Any idea on how do I count if it is not the same year?

M Khalid Junaid Over a year ago

@ShahErhan you can change group by to group by year(date),month and for select month(date) month, year(date)

geoandri · Accepted Answer · 2014-10-26 09:08:10Z

1

In order to count by month and year you should add a year column in your result and add it to group by.

   select YEAR(`date`)  `year`,MONTH(`date`) `month`,
   count(*)
   from test 
   GROUP BY YEAR(`date`), MONTH(`date`) DESC;

answered Oct 26, 2014 at 9:08

geoandri

2,4282 gold badges18 silver badges30 bronze badges

Comments

A.B · Accepted Answer · 2014-10-26 07:53:40Z

0

try this query it would work, may be with some alteration

 "select count(DATE_FORMAT(date_column, '%m')) form your_table group by date_coloumn"

or

 "select count(*),DATE_FORMAT(date_column, '%m') form your_table group by date_coloumn"

answered Oct 26, 2014 at 7:53

A.B

20.5k3 gold badges43 silver badges74 bronze badges

2 Comments

Shah Erhan Over a year ago

I've tried your solution with some alteration but I can't get it right. The other answer works flawlessly. Thanks by the way.

A.B Over a year ago

i have a mistake in form instead of form, any ways purpose is achieved and thats great (Y)

Collectives™ on Stack Overflow

Mysql count month

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related