MySQL - How to eliminate duplicated users in a Ranking?

Question

I have a Ranking on my site - with 2 Tables: USERS and ANSWERS.

ANSWERS TABLE is:

> ======================
> QUIZID ----- USERID ---- TOTALPOINTS ---- TYPE1 -- TYPE2 - TOTALTIME
>   100   ------  22 -------    100      ----- 30 ---- 70 ----  300
>   100  -------- 45 -------    80      ------ 40 -----40 ----- 280
>   101 --------- 45 -------    60 ------------30 -----30 ------200

TYPE1 and TYPE2 are the points acumulated in different categories of questions (like "Sales" and "Marketing") - and IF we have a DRAW, most points in TYPE1 WIN.. Most in TYPE2 (the full table has 11 types)..

HERE : User 22 Have 100 Points - and User 45 have 140 Points.

You can do more than 1 Answer for each user - because we had lots of questions.

MY PROBLEM:

USER TABLE is:

========================
USERID ------ NAME
  22   ------ JOHN
  22 -------- JOHN
  22 -------- JOHN
  45 -------- MARK
=========================

Some users was duplicated on the user table.

I'm using this SELECT to create the ranking (TOP 50):

SELECT users.userid, 
       users.name, 
       Sum(totalpoints), 
       Sum(type1), 
       Sum(type2), 
       Min(totaltime) 
FROM   answers 
       INNER JOIN users 
               ON users.userid = answers.userid 
WHERE  totalpoints > 0 
GROUP  BY users.userid 
ORDER  BY Sum(totalpoints) DESC, 
          Sum(type1) DESC, 
          Sum(type2) DESC, 
          Min(totaltime) 
LIMIT  50 

But..

I have the folow result:

USERID - NAME - TOTALPOINTS - TYPE1 - TYPE2 - TOTALTIME
 22 ---- JOHN -     300 ----- 90 ----- 210 --   900
 45 ---- MARK ---   140 ----- 70 ----- 70 ----  480

USERID 22 is 3x more points because is 3x in USERS TABLE..

There is any way to correct this - without delete the extra users from users table?

(If I delete the users I got the right Ranking.. but I dont correct the problem.. because if tomorrow anyone duplicated again, the problem will return)

tks!!

• I need to ELIMINATE DUPLICATED ROWS. Not Sum.

Answer 1

You can try joining on a subquery, like so:

SELECT users.userid, 
       users.name, 
       Sum(totalpoints), 
       Sum(type1), 
       Sum(type2), 
       Min(totaltime) 
FROM answers 
       INNER JOIN (SELECT DISTINCT * FROM users) AS users 
       ON users.userid = answers.userid 
WHERE totalpoints > 0 
GROUP BY users.userid 
ORDER BY Sum(totalpoints) DESC, 
      Sum(type1) DESC, 
      Sum(type2) DESC, 
      Min(totaltime) 
LIMIT 50 

...where the subquery only includes distinct rows from the users table.