Mysqli query for counting number of total friends

Question

When I have a table of users:

id | login
1    Mike
2    Janet
3    Bruce

And I have a table of friendships called friendlist:

id | user | friend
1    1      2 // Mike is friend with Janet
2    2      1 // Janet accepted, created the friendship!
3    1      3 // Mike is friend with Bruce
4    3      1 // Bruce accepted!

Now, Mike has 2 friends, Janet has 1, Bruce has 1.

What query do I run to count the number of friends like this?

This is not a homework. I've been struggling with this for hours, unable to do this.

A query which works for me, but returns incorrect results (more friends than there really are):

SELECT `users`.`id`, `login`, COUNT(`a`.`id`)
FROM `users`
LEFT JOIN(
   SELECT user AS `id` FROM `friendlist`
   UNION ALL 
   SELECT friend     FROM `friendlist`
) as `a` ON `a`.`id` = `users`.`id`
GROUP BY `users`.`id`

Answer 1

Given your comments in a below post where a friend has to be requested and then the fried has to accept, I have updated my answer:

SELECT U.Id, U.Login, COUNT(DISTINCT FL2.Id) 
FROM Users U
    LEFT JOIN FriendList FL ON U.Id = FL.User
    LEFT JOIN FriendList FL2 ON 
        FL.friend = FL2.user AND FL2.friend = U.Id
GROUP BY U.Id

• SQL Fiddle Demo

I've included using DISTINCT in case there could be duplicate rows -- this may not be the case.

Also, to clarify the difference between using COUNT(*) vs COUNT(FL2.Id), those are 2 different statements. Using COUNT(*) would negate the LEFT JOIN and return all rows as the friend count.

Answer 2

I would create function that will return total count from 2 declared variables. First variable holds count from user column and the other from friends.

1  DELIMITER ;;
2  CREATE FUNCTION `Friendships`(k int) RETURNS int(11)
3  BEGIN
4    DECLARE user int;
5    DECLARE friends int;
6    SELECT count(*) from friendlist WHERE friendlist.user = k into user;
7    SELECT count(*) from friendlist WHERE friendlist.friends = k into friends;
8    RETURN user + friends;
9  END ;;
10 DELIMITER ;

So in the end I would call my function and give it an id (parameter) of a user which number of friendships I want to know.

SELECT friendships(1);

From your example result would be 4.

Hope this helps.