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

Question

If someone were thinking about how to query the vbulletin database to generate a report on the number of registrations per month / year to achieve results such as ..

MM/YYYY Count 01/2001 : 10 02/2001 : 12 ... ... 

Thanks to these answers below. My latest version that works is as follows:

 SELECT COUNT(*) as 'Registrations', YEAR(FROM_UNIXTIME(joindate)) as 'Year', MONTH(FROM_UNIXTIME(joindate)) as 'Month' FROM vbfuser GROUP BY Year,Month 

Answer 1

I am not familiar with the vBulletin database structure, but you should do something like this if your user table has a date / datetime / timestamp created_date or reg_timestamp or something similar using MySQL YEAR () and MONTH () functions .

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

This will lead to something like this:

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

Edit: regarding Dave's comment: The vBulletin date seems to be stored in Unixtime format. In this case, just wrapping the column using FROM_UNIXTIME converts it to a readable MySQL date:

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

Answer 2

vBulletin stores daily statistics, so it’s best to work with this table. This is not a big problem for counting new registrations, but if you want to count the number of posts, it will eventually become expensive. Here is an example that does what you want:

 SELECT DATE_FORMAT(FROM_UNIXTIME(dateline), '%m/%Y') AS month, SUM(nuser) AS new_users FROM stats GROUP BY month ORDER BY dateline 

Answer 3

You need to group by month field:

 select MONTHYEAR month , count(*) from tabname group by MONTHYEAR 

with this, you will read all the months and count different lines, getting the necessary information.

If you provide a user table this might be more useful;)