MySQL query to return only duplicate entries with counts

Question

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_id-address_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.

Answer 1

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

Answer 2

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 (>_<)

Answer 3

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