Skylight_lady Posted January 17, 2011 Share Posted January 17, 2011 Hi guys, how do i get the the total amount of users per month. I am trying to use an sql statement to do this but so far i can't seem to find the info online for the correct statement to use. My code is as follows: SELECT * FROM table1, table2 WHERE table1.userID = table2.ID AND table1.RegisterDate = MONTH (current timestamp) I will also need another sql to give me the total amt of users per year. Quote Link to comment https://forums.phpfreaks.com/topic/224739-sql-to-get-all-registered-users-per-month/ Share on other sites More sharing options...
Pikachu2000 Posted January 17, 2011 Share Posted January 17, 2011 Assuming the date is stored in DATE or DATETIME field, this will do the comparison correctly. . . . AND MONTH(table1.RegisterDate) = MONTH(CURDATE()) Quote Link to comment https://forums.phpfreaks.com/topic/224739-sql-to-get-all-registered-users-per-month/#findComment-1160860 Share on other sites More sharing options...
mikosiko Posted January 17, 2011 Share Posted January 17, 2011 total amount of users per month and probably you will need a SUM and a GROUP by clause Quote Link to comment https://forums.phpfreaks.com/topic/224739-sql-to-get-all-registered-users-per-month/#findComment-1160861 Share on other sites More sharing options...
Skylight_lady Posted January 17, 2011 Author Share Posted January 17, 2011 . . . AND MONTH(table1.RegisterDate) = MONTH(CURDATE()) It's a DATETIME field. I forgot to mention that. It's perfect. Works for the year too. How would you get last years users? is it by changing the code like: YEAR(CURDATE() - 1) Quote Link to comment https://forums.phpfreaks.com/topic/224739-sql-to-get-all-registered-users-per-month/#findComment-1160867 Share on other sites More sharing options...
Skylight_lady Posted January 17, 2011 Author Share Posted January 17, 2011 It actually work like this: YEAR(CURDATE()) - 1 Cheers guys. Quote Link to comment https://forums.phpfreaks.com/topic/224739-sql-to-get-all-registered-users-per-month/#findComment-1160871 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.