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.

brendan · Accepted Answer · 2009-02-03 20:10:49Z

8

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

edited Feb 3, 2009 at 20:10

answered Feb 3, 2009 at 20:05

brendan

30.1k20 gold badges70 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Todd R · Accepted Answer · 2009-02-03 20:06:52Z

2

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

answered Feb 3, 2009 at 20:06

Todd R

18.6k8 gold badges34 silver badges41 bronze badges

Comments

TJ L · Accepted Answer · 2009-02-03 20:06:08Z

1

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

answered Feb 3, 2009 at 20:06

TJ L

24.5k7 gold badges63 silver badges78 bronze badges

Collectives™ on Stack Overflow

MySQL query to return only duplicate entries with counts

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related