php - How to write an SQL query that counts the number of rows per month and year? -

had idea how query vbulletin database generate report on number of registrations per month/year achive results like..

mm/yyyy      count 01/2001  :   10 02/2001  :   12 ... ... 

thanks answers below.. final version works follows:

select    count(*) 'registrations',    year(from_unixtime(joindate)) 'year',   month(from_unixtime(joindate)) 'month' vbfuser group year,month 

i not familiar vbulletin's database structure, should something this, assuming user table has date/datetime/timestamp created_date or reg_timestamp column or similiar, using mysql's year() , month() functions.

select      count(*) count,      year(reg_timestamp) year      month(reg_timestamp) month users  group year, month; 

this result in similiar this:

+-------+-------+------+ | count | month | year | +-------+-------+------+ |     4 |    11 | 2008 |  |     1 |    12 | 2008 |  |   196 |    12 | 2009 |  |   651 |     1 | 2010 |  +-------+-------+------+ 

edit: regarding dave's comment: vbulletin's date seems stored in unixtime format. in case, wrapping column from_unixtime convert readable mysql date:

select      count(*) count,      year(from_unixtime(reg_timestamp)) year      month(from_unixtime(reg_timestamp)) month users  group year, month;