How can I count the non-unique combinations of values in MySQL?

Question

I have a table with some legacy data that I suspect may be a little messed up. It is a many-to-many join table.

LIST_MEMBERSHIPS
----------------
list_id
address_id

I'd like to run a query that will count the occurrences of each list_id-address_id pair and show the occurrence count for each from highest to lowest number of occurrences.

I know it's got to involve COUNT() and GROUP BY, right?

Answer 1

select list_id, address_id, count(*) as count
from LIST_MEMBERSHIPS
group by 1, 2
order by 3 desc

You may find it useful to add

having count > 1

Answer 2

select count(*), list_id, address_id
from list_membership
group by list_id, address_id
order by count(*) desc