sql query for fetching leaderboard from two tables

I have two MySql table user and marks

User table:

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int          | NO   | PRI | NULL    | auto_increment |
| firstName | varchar(255) | YES  |     | NULL    |                |
| lastName  | varchar(255) | YES  |     | NULL    |                |
| email     | varchar(55)  | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

Marks Table

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int          | NO   | PRI | NULL    | auto_increment |
| user_id      | int          | NO   |     | NULL    |                |
| subject_id   | varchar(255) | YES  |     | NULL    |                |
| score        | int          | YES  |     | NULL    |                |
| subject_name | varchar(225) | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

I want to fetch details(userid, firstName and lastName) of top 10 users with the highest marks in descending order. Marks of the user is defined as sum of all scores a user has in different subjects.

I am really confused which join is to be used here, I am new to MySql and this query is kind of challenging for me, Hope you understood the problem. Please let me know if you have any suggestion, Thank You

Answer

You can join the two tables together by id=user_id, then you group the result by id, sort by the total marks per id, then take the top 10 results.

If you wanted a result even if the user had no marks at all, change JOIN to LEFT JOIN, this will still give you a result from the first table even if there are no results from the second.

SELECT u.id, u.firstName, u.lastName, SUM(m.score) AS TotalScore
FROM [User] AS u
JOIN Marks AS m ON m.user_id = u.id
GROUP BY u.id, u.firstName, u.lastName
ORDER BY SUM(m.score) DESC
LIMIT 10;