#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Posts
    19
    Rep Power
    0

    Help with email address group by query


    running mysql 5.5 and I have a table with email address and I need to compile a list of email address grouped by whatever is in front of the @ descending by count.

    such as :

    webmaster 500
    sales 400
    info 300


    etc.

    the field name in the table is of course 'email' any help would be appreciated

    thanks in advance.

    Jeff
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Posts
    19
    Rep Power
    0
    after some research I came up with this....to get the top 10,000

    SELECT substring_index(email, '@', 1), COUNT(*) AS MyCount FROM mytable GROUP BY substring_index(email, '@', 1) ORDER BY MyCount DESC limit 10000;

IMN logo majestic logo threadwatch logo seochat tools logo