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
The table has:
contact_ident contact_firstname contact_lastname contact_title contact_phone contact_phone2 contact_phone3 contact_email contact_address contact_address2 contact_city contact_state contact_zip contact_notes contact_client_ident contact_select
Thanks.
David,
The syntax error is the "as contact_name" because the DISTINCT function does not know how to handle this extra parameter, and if you remove "as Contact_Name", it should work. Also replace your "ORDER BY contact_name" with "ORDER BY 1" to indicate you want to sort on the first column. I would also replace the "as Count" with "as Num" so it is not confused with the Count() function. You are also grouping by "contact_ident" which doesn't appear in the Select list and sorting by "contact_name". This will work but you may want to put the group by column "contact_ident" in the select columns so people know how it is grouped. (Just a suggestion)
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
