mysql - Return number of users for each month by type -


i not know how possible is:

i have database in (sqlfiddle)[http://sqlfiddle.com/#!9/d7a8f/3], database want return following :

i want return number of user created in each month based on type. there 4 role in database (administrator, no role, super user, user).

just elaborate on query how want information like: e.g. sql picture

january 2016, 4 , 3 ,5, 8,20. january 2015 month, 4 administrators, 3 no role, 5 super user, 8 users, , 20 overall total. user type must sorted in ascending order.

i have tried following:

select date_format(timestamp,' %b %y') month,usertype,count(userid) user group month,usertype 

you use bunch of count functions on case expressions break data respective roles:

select   date_format(timestamp, '%b %y') `month`,          count(case usertype when 'administrator' 1 end) administrator,          count(case usertype when 'no role' 1 end) norole,          count(case usertype when 'super user' 1 end) superuser,          count(case usertype when 'user' 1 end) `user`     `user` group date_format(timestamp, '%b %y') 

Comments

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

css - Make div keyboard-scrollable in jQuery Mobile? -

ruby on rails - Seeing duplicate requests handled with Unicorn -