mysql メールアドレスからドメイン数でカウント

+-------+----------------------+
| id    | mail_address         |
+-------+----------------------+
|     1 | aaaa@example.com     |
|     2 | bbbb@example.com     |
|     3 | cccc@example2.com    |
|     4 | dddd@example2.com    |
|     5 | eeee@example2.com    |
|     6 | ffff@example3.com    |
|     7 | gggg@example4.com    |
+-------+----------------------+

こんなデータあったら

SELECT
COUNT(id) AS ccc,
SUBSTRING_INDEX(mail_address,'@',-1) as domain
FROM mail_data 
WHERE id in(select uid from order_master )
GROUP BY domain
ORDER BY ccc;

ついでなんでソート

+-----+-----------------+
| ccc | domain          |
+-----+-----------------+
|   1 | example4.com    |
|   1 | example3.com    |
|   2 | example.com     |
|   3 | example2.com    |
+-------+---------------+

的な感じ