Policz sumy według roku i miesiąca

Mam tabelę, która wygląda tak: 

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'

Jak wybrać i pogrupować je tak, aby wynik był: 

year,month,total
2011,1,2
2011,2,1
Author: ekad, 2011-03-02
Source

2 answers

Spróbuj tego: 

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')
 45
Author: Adam Lukaszczyk,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-03-02 10:50:05 
SELECT YEAR(created) as year_val, MONTH(created) as month_val ,COUNT(*) as total
FROM testing
GROUP BY YEAR(created), MONTH(created)
 6
Author: Shakti Singh,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-03-02 10:55:26