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:

1|100
2|75
3|10
4|2
5|1