user2636556 user2636556 - 4 months ago 12
PHP Question

Count registered users by date

I need some help with this MySQL query. I have the following fields in the DB

id created_on
1 2016-02-15 12:47:09
2 2016-02-24 12:47:09
3 2016-02-29 12:47:09
4 2016-03-11 12:47:09
5 2016-03-15 12:47:09
6 2016-03-22 12:47:09
7 2016-04-10 12:47:09
8 2016-04-11 12:47:09


I need to count the total records for the past 4 months. i did this so far,

SELECT YEAR(created_on) as year_val, MONTH(created_on) as month_val ,COUNT(*) as total
FROM `user`
GROUP BY YEAR(created_on), MONTH(created_on)


thanks

Answer

add

WHERE created_on >= DATE_ADD(CURDATE(), INTERVAL - 4 MONTH)

it will return

"year_val"   "month_val"   "total"
"2016"       "3"           "1"
"2016"       "4"           "2"
"2016"       "5"           "1"