Hello, how can you identify the values ​​that are repeated and show how many times, well, from the largest repetitions to the smallest table is called for example table1 and the lines:

phoenix @ centrum.cz _petr_riha@icloud.com money @ email.cz milena @ centrum.cz

It is necessary to identify repetitions after the @ sign, I want to understand which email is most used

    1 answer 1

    So:

    SQL feeddle

    MySQL 5.6 Schema Setup :

    CREATE TABLE table1 (`email` varchar(255)) ; INSERT INTO table1 (`email`) VALUES ('foo@gmail.com'), ('bar@gmail.com'), ('root@mail.ru') ;

    Query 1 :

     select count(1) cnt, SUBSTRING_INDEX(email, '@', -1) as domain from table1 group by domain having cnt>1 order by cnt desc

    Results :

     | cnt | domain | |-----|-----------| | 2 | gmail.com |
    • Just in case, if you need exactly those that repeat, and do not meet only once. Add to group by having count(1) > 1 - Sergey
    • @Sergey added - Yura Ivanov
    • thanks a lot, it works. @YuraIvanov - t0rick