CODEWITHSUNDEEP
sqlmysql
Ethan
Ethan

Reputation: 60179

MySQL query to return only duplicate entries with counts

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.

Upvotes: 1

Views: 5200

Answers (3)

brendan
brendan

Reputation: 30006

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

Upvotes: 8

TJ L
TJ L

Reputation: 24472

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

Upvotes: 1

Todd R
Todd R

Reputation: 18526

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

Upvotes: 2

Related Questions