[MYSQL] GROUP BY, COUNT

select a.email as email, count(a.email) as count_amail
from
(SELECT
 SUBSTRING(email, INSTR(email ,'@')+ 1,LENGTH(RTRIM(email))) as email
FROM
 mailservice) a
group by a.email
order by count_amail desc