I want to ranking result with city based in mysql

Question

I want to ranking result with city based in mysql

table1:-users

| user_id | marks  |
--------------------
|   1     |   10   |
|   5     |   10   |
|   5     |   50   |
|   3     |   15   |
|   4     |   10   |

|   2     |   10   |
|   6     |   10   |
|   6     |   50   |
|   4     |   15   |
|   4     |   10   |

table:-2 users details

| user_id | city         |
--------------------
|   1     |   newdelhi   |
|   2     |   kolkata    |
|   3     |   mumbai     |
|   4     |   newdelhi   |
|   5

|   6     |   newdelhi   |

I want to result like this:

| user_id | points |
--------------------
|   6     |   60   |
|   4     |   35   |
|   1     |   10   |

Answer 1

Try this :

SELECT 
    users.user_id 
    ,SUM(users.marks) AS points
FROM 
    users
INNER JOIN 
    users_details ON users.user_id = users_details.user_id
WHERE 
    users_details.city = 'newdelhi'
GROUP BY 
    user_id