Count the amount of likes a user received on each post sql

I have two tables. Likes table which contains it’s like_id (PK), vole_id, user_id, and created_at timestamp. And another table called voles that contain vole_id (PK), and user_id, as well as a bunch of other columns that will not matter for this problem.

The user_id for the voles tables is the person who posted. I want to get the amount of likes a person has on all their voles combined to show in their profile page.

voles table:

likes table:

Of course there will be more than one post for a user and more than one like on each post. In this case, user_id 21 has 1 like on vole_id 28.

I honestly don’t know where to start, I know the basics of SQL, but I’m still not the best at it. Any help is appreciated!

Answer

You just need to count records in the likes table joining it with voles filtering by user_id:

SELECT COUNT(*)
FROM likes l
JOIN voles v on (l.vole_id=v.vole_id)
WHERE v.user_id=21 // here is a user id for whom we count all likes