Get All Active Users Count Role Wise:
SELECT
SUM(CASE WHEN a.user_type = '1' THEN 1 ELSE '0' END) AS 'Super Admin',
SUM(CASE WHEN a.user_type = '2'THEN 1 ELSE '0' END) AS 'CC',
SUM(CASE WHEN a.user_type = '3' THEN 1 ELSE '0' END) AS 'Executive',
SUM(CASE WHEN a.user_type = '5' THEN 1 ELSE '0' END) AS 'ABM',
SUM(CASE WHEN a.user_type = '4' THEN 1 ELSE '0' END) AS 'DM',
SUM(CASE WHEN a.user_type = '6' THEN 1 ELSE '0' END) AS 'CH',
SUM(CASE WHEN a.user_type = '16' THEN 1 ELSE '0' END) AS 'Mini User',
SUM(CASE WHEN a.user_type = '17' THEN 1 ELSE '0' END) AS 'Superadmin Dealer',
SUM(CASE WHEN a.user_type = '19' THEN 1 ELSE '0' END) AS 'Referral User',
SUM(CASE WHEN a.user_type = '20' THEN 1 ELSE '0' END) AS 'Bulk Upload User',
SUM(CASE WHEN a.user_type = '24' THEN 1 ELSE '0' END) AS 'Dealer Master',
SUM(CASE WHEN a.user_type = '25' THEN 1 ELSE '0' END) AS 'Broker Master',
SUM(CASE WHEN a.user_type = '26' THEN 1 ELSE '0' END) AS 'Lead Delete',
SUM(CASE WHEN a.user_type = '27' THEN 1 ELSE '0' END) AS 'Mfsetu CRE',
SUM(CASE WHEN a.user_type = '28' THEN 1 ELSE '0' END) AS 'Tractor Admin',
SUM(CASE WHEN a.user_type = '29' THEN 1 ELSE '0' END) AS 'DMT Admin',
SUM(CASE WHEN a.user_type = '30' THEN 1 ELSE '0' END) AS 'Mahindra New Car',
SUM(CASE WHEN a.user_type = '31' THEN 1 ELSE '0' END) AS 'New Car Admin',
SUM(CASE WHEN a.user_type = '32' THEN 1 ELSE '0' END) AS 'CH Mentor',
SUM(CASE WHEN a.user_type = '33' THEN 1 ELSE '0' END) AS '3-Wheeler Admin',
SUM(CASE WHEN a.user_type = '35' THEN 1 ELSE '0' END) AS 'Auto Fin Admin' FROM `user_login` `a` LEFT JOIN `user_info` `b` ON `a`.`id`=`b`.`uid` WHERE a.user_type IN('1', '2', '4', '5', '6', '16', '17', '19', '20', '24', '25', '26', '27', '28', '29', '30','31','32','33','35') AND a.user_status='1' ORDER BY a.user_type
Get All Active Users Count App and Role Wise:
SELECT
SUM(CASE WHEN a.user_type = '21' AND a.user_category = 'Dealer' THEN 1 ELSE '0' END) AS 'Mfsetu App Active Dealers',
SUM(CASE WHEN a.user_type = '21' AND a.user_category = 'Broker' THEN 1 ELSE '0' END) AS 'Mfsetu App Active Brokers',
SUM(CASE WHEN a.user_type = '18' THEN 1 ELSE '0' END) AS 'R2W App Active Users',
SUM(CASE WHEN a.user_type = '3' THEN 1 ELSE '0' END) AS 'LMS App Active Executives',
SUM(CASE WHEN a.user_type = '5' THEN 1 ELSE '0' END) AS 'LMS App Active ABMs',
SUM(CASE WHEN a.user_type = '4' THEN 1 ELSE '0' END) AS 'LMS App Active DMs',
SUM(CASE WHEN a.user_type = '14' THEN 1 ELSE '0' END) AS 'LMS App Active CAT B MFCWL Executive',
SUM(CASE WHEN a.user_type = '13' THEN 1 ELSE '0' END) AS 'LMS App Active CAT B RTO Agent',
SUM(CASE WHEN a.user_type = '15' THEN 1 ELSE '0' END) AS 'LMS App Active CAT B Dealer',
SUM(CASE WHEN a.user_type = '22' THEN 1 ELSE '0' END) AS 'LMS App Active SBC Executive',
SUM(CASE WHEN a.user_type = '23' THEN 1 ELSE '0' END) AS 'LMS App Active DMT Executive'
FROM `user_login` `a` LEFT JOIN `user_info` `b` ON `a`.`id`=`b`.`uid` WHERE a.user_type IN('21','18','3','5','4','14','13','15','22','23') AND a.user_status='1' ORDER BY a.user_type
Get All Active Users and Accessed Module Wise:
SELECT * FROM `master_track_ss_map_api` WHERE trackfor !='Selfinspection module' GROUP by exe_id
