Viktor Viktor - 1 year ago 94
PHP Question

MySQL Group By Count Substring

I'm working on a script to identify which email providers in our database are the most popular. URL links is the name of the table and 'contact_email' is the contact email address associated with the link.

I wrote the following MySQL query

group by contact_email order by count(url_links.contact_email) DESC

This query returns all of the emails and the count of the person who submitted the listing. What I'd like to do is modify the MySQL script so that it gives me a count of the whole provider. i.e.,, etc.

I could read this entire array into PHP and do an 'explode' on the @ sign and group it that way, however I'm curious if the community knows how to modify this script so that instead of the count being each email address - it gives back a count of ALL email addresses in Gmail, Yahoo etc. in the group by

Aka a 'Group By Substring' where all the items would be grouped by everything after the '@'sign.


Answer Source

you can group and count by:

right(url_links.contact_email, length(url_links.contact_email)-INSTR(url_links.contact_email, '@'))

something like this should work:

right(url_links.contact_email, length(url_links.contact_email)-INSTR(url_links.contact_email, '@')), count(right(url_links.contact_email, length(url_links.contact_email)-INSTR(url_links.contact_email, '@')))
group by right(url_links.contact_email, length(url_links.contact_email)-INSTR(url_links.contact_email, '@')) order by count(right(url_links.contact_email, length(url_links.contact_email)-INSTR(url_links.contact_email, '@'))) DESC