SELECT owners_per_item, COUNT(owners_per_item) AS distribution FROM ( SELECT item_id, COUNT(item_id) AS owners_per_item FROM items_owners GROUP BY item_id ) AS sub GROUP BY owners_per_item
I run a book reading/tracking site and wanted to know the popularity of each book based on its distribution amongst users (basically, how many users own each book). I've made the SQL a bit more generic so you can reuse it to measure distribution of any Many-Many relationship
We have an item (book) which has a Many-Many relationship with an owner (user) which is stored in a mapping table (books_users). We don't just want to know the number of owners each item has (how many shelves you can find each book on), but what how many items have only one owner, how many have two owners and so on.
The subquery here:
SELECT item_id, COUNT(item_id) AS owners_per_item FROM items_owners GROUP BY item_id
will return a list of [item_id, number of owners that item has] which we can then feed into the main query above. This will output something like: