Have Geek Brain

How can I count unrepeatable combinations of values ​​in MySQL?

I have a table with some stale data that I suspect might get a little messy. This is a many-to-many join table.

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

I would like to run a query that will count the occurrences of each pair list_id

- address_id

and show the number of matches for each of the highest and lowest number of occurrences.

I know what it was supposed to include COUNT()

and GROUP BY

, right?

0


a source to share


2 answers 


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

You may find it helpful to add



having count > 1
+4


a source 


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


+1


a source






More articles:


All Articles