Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT `users`.`date`,
- `verified`.`count` AS `verified emails`,
- `non_verified`.`count` AS `non verified emails`,
- `users`.`count` AS `total registrations`,
- CONCAT(ROUND(( `verified`.`count` / `users`.`count` * 100 ), 2), '%') AS `verified percentage`,
- CONCAT(ROUND(( `non_verified`.`count` / `users`.`count` * 100 ), 2), '%') AS `non verified percentage`
- FROM (
- SELECT DATE_FORMAT(`registration_date`, '%Y - %m') AS `date`, COUNT(*) AS `count`
- FROM `tbl_user`
- GROUP BY DATE_FORMAT(`registration_date`, '%Y%m')
- ) AS `users`
- LEFT JOIN (
- SELECT DATE_FORMAT(`registration_date`, '%Y - %m') AS `date`, COUNT(*) AS `count`
- FROM `tbl_user`
- WHERE `email_verified` = 0
- GROUP BY DATE_FORMAT(`registration_date`, '%Y%m')
- ) AS `non_verified` ON `users`.`date` = `non_verified`.`date`
- LEFT JOIN (
- SELECT DATE_FORMAT(`registration_date`, '%Y - %m') AS `date`, COUNT(*) AS `count`
- FROM `tbl_user`
- WHERE `email_verified` = 1
- GROUP BY DATE_FORMAT(`registration_date`, '%Y%m')
- ) AS `verified` ON `users`.`date` = `verified`.`date`
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement