CODEWITHSUNDEEP
phpmysql
Viktor
Viktor

Reputation: 547

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

SELECT
url_links.contact_email,count(url_links.contact_email)
FROM
url_links
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. Gmail.com, Yahoo.com, Hotmail.com 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.

Thanks!

Upvotes: 0

Views: 234

Answers (1)

Cesar
Cesar

Reputation: 717

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:

SELECT
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, '@')))
FROM
url_links
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

Upvotes: 1

Related Questions