4

Had anyone any idea how to query a vbulletin database to generate a report on the number of registrations per month/year to achive results like..

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

Thanks to those answers below.. My final 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
Aron Rotteveel
  • 81,193
  • 17
  • 104
  • 128
David Brown
  • 3,021
  • 3
  • 26
  • 46

3 Answers3

7

I am not familiar with vBulletin's database structure, but you should do something like this, assuming your user table has a date/datetime/timestamp created_date or reg_timestamp column or something similiar, using MySQL's 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 result in something similiar to 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 to be stored in Unixtime format. In this case, simply wrapping the column with FROM_UNIXTIME will convert 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;
Aron Rotteveel
  • 81,193
  • 17
  • 104
  • 128
  • 1
    Thank you kindly.. Only thing to be aware of for others who read this is the vbulletin date format is in UNIX date format and needs converting. So look at my original question for formatted syntax – David Brown Feb 18 '11 at 12:51
  • 1
    @Dave Thanks for clearing that up. I added an extra example for this specific case. – Aron Rotteveel Feb 18 '11 at 12:54
0

vBulletin keeps daily stats, so it may be nicer to work off that table. It isn't much of a problem for counting new registrations but if you want to count posts it gets expensive eventually. Here's an example which 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
Conor McDermottroe
  • 1,333
  • 9
  • 10
-1

You have to group by the field month:

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

with this, you would read all monthyears and count the different rows, obtaining the info you need.

If you provide the users table, it could be of more help ;)

SoulWanderer
  • 315
  • 3
  • 11