Need help with a query

webosb · 01-11-2008, 08:10 PM

Right now, I am using this query to grab all the email domain names from all my users:

SELECT DISTINCT REVERSE(LEFT(REVERSE(email),LOCATE('@',REVERSE(ema il)) - 1)) AS domain FROM users ORDER BY domain

Is there a way to alter this query where it will return 2 columns of data, 1 with the results from above and 1 with count of users with the domain?

Thanks in advance

RobertK · 01-11-2008, 08:50 PM

Um, that's what we call obfuscated code. That's really a wacky query, no offense meant. How about:

sql Code:

SELECT DISTINCT SUBSTRING(email, LOCATE('@', email)+1) AS domain FROM users ORDER BY domain

I'm not particularly good at SQL yet, so I'm not sure how you'd count users per domain. It could be group-by or a subquery as a variable name.

Village Idiot · 01-11-2008, 09:03 PM

I'm don't believe there is a way to group things by a part of a value using mysql.

Wildhoney · 01-11-2008, 09:31 PM

The HAVING clause will allow you to use a value. You can also use variables in MySQL, like so:

sql Code:

SELECT @myVar:=COUNT(myColumn)

Salathe · 01-11-2008, 09:38 PM

My version of MySQL (5.0.27) allows me to make the following query without any complaints:

SQL Code:

SELECT DISTINCT 
    SUBSTRING(email, LOCATE('@', email) + 1) AS domain,
    COUNT(*) AS domain_count
FROM 
    users
GROUP BY 
    domain
ORDER BY 
    domain

webosb · 01-11-2008, 10:12 PM

Quote:

Originally Posted by Salathe

My version of MySQL (5.0.27) allows me to make the following query without any complaints:

SQL Code:

SELECT DISTINCT 
    SUBSTRING(email, LOCATE('@', email) + 1) AS domain,
    COUNT(*) AS domain_count
FROM 
    users
GROUP BY 
    domain
ORDER BY 
    domain

This is exactly what I wanted. I'm still new to GROUP BY. Thanks alot Salathe!