Hi all. Can somebody explain to me why the following SELECT doesn't work?:
SELECT COUNT(DISTINCT(CONCAT(contact_firstname,' ',contact_lastname) as contact_name)) as count FROM Contacts GROUP BY contact_ident ORDER BY contact_name;
The error returned is:
ERROR 1064: You have an error in your SQL syntax near 'as contact_name)) as count FROM Contacts GROUP BY contact_ident ORDER BY contact' at line 1
You can't alias a part of an expression. Try this:
SELECT COUNT(DISTINCT(CONCAT(contact_firstname,' ',contact_lastname))) as count FROM Contacts
Your example included GROUP BY contact_ident... do you have multiple records in this table with the same contact_ident? Trying to find the duplicates? Then try something like this:
SELECT contact_ident, COUNT(*) as count FROM Contacts GROUP BY contact_ident HAVING count > 1
...and maybe this:
SELECT contact_ident, CONCAT(contact_firstname,' ',contact_lastname) contact_name, COUNT(*) as count FROM Contacts GROUP BY contact_ident,contact_name ORDER BY contact_name HAVING count > 1
...or even this:
SELECT CONCAT(contact_firstname,' ',contact_lastname) contact_name, COUNT(*) as count FROM Contacts GROUP BY contact_name HAVING count > 1
It's hard to give better examples as I don't know exactly what you are trying to achieve... :)
-- Roger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
