Join 2 tables on foreign key while using count() in SQL-CodePudding

So I have two tables: Please see the ER diagram here

I want to use SELECT to create one table with "name" from the USER table, "id" as the foreign key for the two tables, and the count of friend_id as the number of friends each user has.

Here is my code:

SELECT name, id, (SELECT count(friend_id) as number
    FROM friend
    GROUP BY user_id)
    FROM user
ORDER BY number DESC

I'm wondering what's the problem with these lines. Thank you!

CodePudding user response：

You can use a subquery to calculate the count.

SELECT name, id, COALESCE(f.Count, 0) AS friend_count
FROM user u
LEFT JOIN (
    SELECT user_id, COUNT(DISTINCT friend_id) AS Count
    FROM friend
    GROUP BY user_id
) f ON f.user_id = u.id
ORDER BY friend_count DESC

I used a LEFT JOIN so that if a user doesn't have a row in friend, it will still return a row with a friend count of 0 (thanks to COALESCE). I also added a DISTINCT so that if the friend has duplicates the friend is counted only one, might not be necessary especially if you have a UNIQUE INDEX setup on columns user_id, friend_id

CodePudding user response：

Just add where to find only one id and remove group by because you have only one id for one or more friends as your diagram says.

SELECT name, id, (SELECT count(friend_id) as number
    FROM friend
    WHERE user_id = user.id)
    FROM user
ORDER BY number DESC

CodePudding user response：

I think this will be correct for you puprose

CREATE TABLE #user(
    id VARCHAR(22),
    [name] VARCHAR(255),
)

CREATE TABLE #friend(
    user_id VARCHAR(22),
    friend_id VARCHAR(22)
)


SELECT name, id, (SELECT COALESCE(COUNT(friend_id), 0)
    FROM #friend f
    WHERE f.user_id = u.id
    GROUP BY user_id) as number
    FROM #user u
ORDER BY number DESC

--Same query with join:

SELECT u.[name], u.id, COALESCE(COUNT(f.friend_id),0) number
FROM #user u
LEFT JOIN #friend f ON f.user_id = u.id
GROUP BY u.[name], u.id
ORDER BY number