Given a table users_groups

How do I find the number of friends for each user, where a friend is defined as a someone in the same group as a user?

CodePudding user response：

I think I understand the ask. You want the sum of the friends by the groupId?

SELECT groupID, SUM(numFriends) AS FriendsinGroup
  FROM [user_groups] AS A
  INNER JOIN [NumofFriendstbl] AS B
  ON A.UserID = B.UserID
  GROUP BY groupID

CodePudding user response：

You can join the table to itself, and count them:

select Me.UserId,  count(Friends.UserId)  as Friends
from user_groups as Me
     left join
     user_groups as Friends
     on Me.groupId=Friends.groupId
     and Me.userId<>Friends.userId
group by Me.UserId

Beware though, this can count the same user more than once if the user is related to the current user via more than one group. You might like to change to count(distinct Friends,UserId), if you don't want that.