Count totals by year and month

mysql date group-by aggregate
Tom picture Tom · Mar 2, 2011 · Viewed 25.1k times · Source

I have a table that looks like this:

id,created,action
1,'2011-01-01 04:28:21','signup'
2,'2011-01-05 04:28:21','signup'
3,'2011-02-02 04:28:21','signup'

How do I select and group these so the output is:

year,month,total
2011,1,2
2011,2,1

Answer

Adam Lukaszczyk picture Adam Lukaszczyk · Mar 2, 2011

Try this:

SELECT DATE_FORMAT(created, '%Y') as 'year',
DATE_FORMAT(created, '%m') as 'month',
COUNT(id) as 'total'
FROM table_name
GROUP BY DATE_FORMAT(created, '%Y%m')

Related questions

MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More
Group by month and year in MySQL

Given a table with a timestamp on each row, how would you format the query to fit into this specific json object format. I am trying to organize a json object into years / months. json to base the query off: { "2009":["…

Read More
MySQL cumulative sum grouped by date

I know there have been a few posts related to this, but my case is a little bit different and I wanted to get some help on this. I need to pull some data out of the database that is …

Read More