I have a table that has two columns:
In order to get the number of friends a user has has, I'd do something like:
SELECT COUNT(user_id) AS num_friends FROM friends WHERE friend_id = some_id
What I want to do is get the number of friends every single user in the table has and cache the results to another table so I don't have to run an expensive query like the one above. Basically a 'rollup'.
What's the best way to query for this data to get the number of friends that every
friend_id has? I was thinking something like this, but it seems very wrong, especially if you have a large amount of distinct
friend_id instances in the table:
SELECT COUNT(*) num_friends, friend_id FROM friends WHERE friend_id IN ( SELECT DISTINCT(friend_id) FROM FRIENDS) GROUP BY friend_id