unknown column

[ejaboneta]

I'm trying to make a "find duplicates" script. My query counts how many rows each "firstname lastname" combination is in the database. I want to only retrieve rows with more than one count. I get an unknown 'counter' column error when I run it. How can I get around this or is there a better way?

 

 

SELECT firstname, lastname, count( concat( firstname, lastname ) ) AS counter

FROM leads

WHERE `counter` > 1

GROUP BY concat( firstname, lastname )

ORDER BY `counter` DESC

[Mchl]

Can't use aliases in WHERE clause. Use HAVING instead

 

SELECT firstname, lastname, count( concat( firstname, lastname ) ) AS counter
FROM leads
HAVING `counter` > 1
GROUP BY concat( firstname, lastname )
ORDER BY `counter` DESC

[ejaboneta]

Thanks! But just in case anyone looks to this post for help... HAVING should go after GROUP BY....

 SELECT firstname, lastname, count( concat( firstname, lastname ) ) AS counter
FROM leads
GROUP BY concat( firstname, lastname )
HAVING counter >1
ORDER BY `counter` DESC
LIMIT 0 , 30