We recently installed a "like" extension on our phpbb message board, which is stored in a table we'll call "likes". I've been struggling to construct a query I've called "admirer", where it counts how many likes one user has given another. The output would ideally look like the following:

user_id_to | like count | user_id_from
    sam    |      6     |      mike
    sam    |      3     |      john
    mike   |      4     |      sam
    john   |      5     |      mike

Hopefully that helps. I have a query where it counts how many likes users get from a specified user_id in the WHERE clause as shown below:

SELECT users.username, COUNT(likes.like_id) AS love
FROM likes LEFT JOIN users ON users.user_id = likes.user_id_to
WHERE likes.user_id_from = [someone's user id]
GROUP BY users.username

However, I'm struggling to modify it without the WHERE, and have it display the count of likes from any user to any user. Any suggestions would be greatly appreciated.

  • what is the structure of your "likes" table? Oct 28, 2016 at 18:16
  • like_id, user_id_from, user_id_to, like_time are the relevant attributes
    – sxmrxzxr
    Oct 28, 2016 at 18:54
  • Get rid of id, you have a 'natural' PRIMARY KEY(user_id_from, user_id_to).
    – Rick James
    Oct 28, 2016 at 20:50

1 Answer 1


Two things:

  1. Group by both user IDs
  2. Inner join to the users' table two times, use different alias to join it with different user id

    SELECT u_to.username as user_to, u_from.username as user_from, count(*) as like_count 
    FROM likes
    INNER JOIN users u_to on likes.user_id_to=u_to.user_id
    INNER JOIN users u_from on likes.user_id_from=u_from.user_id
    GROUP BY u_to.username, u_from.username
    ORDER BY like_count DESC
  • perfect, much appreciated
    – sxmrxzxr
    Oct 28, 2016 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.