sql – MySQL query to return only duplicate entries with counts – Education Career Blog

I have a legacy MySQL table called lnk_lists_addresses with columns list_id and address_id. I’d like to write a query that reports all the cases where the same list_idaddress_id combination appears more than once in the table with a count.

I tried this…

SELECT count(*), list_id, address_id
FROM lnk_lists_addresses
GROUP BY list_id, address_id
ORDER BY count(*) DESC
LIMIT 20

It works, sort of, because there are fewer than 20 duplicates. But how would I return only the counts greater than 1?

I tried adding “WHERE count(*) > 1” before and after GROUP BY but got errors saying the statement was invalid.

,

SELECT count(*), list_id, address_id
FROM lnk_lists_addresses
GROUP BY list_id, address_id
HAVING count(*)>1
ORDER BY count(*) DESC

To combine mine and Todd.Run’s answers for a more “complete” answer. You want to use the HAVING clause:

http://dev.mysql.com/doc/refman/5.1/en/select.html

,

You want to use a “HAVING” clause. Its use is explained in the MySQL manual.

http://dev.mysql.com/doc/refman/5.1/en/select.html

,

SELECT count(*) AS total, list_id, address_id
FROM lnk_lists_addresses
WHERE total > 1
GROUP BY list_id, address_id
ORDER BY total DESC
LIMIT 20

If you name the COUNT() field, you can use it later in the statement.

EDIT: forgot about HAVING (>_<)

Leave a Comment