CODEWITHSUNDEEP
mysql
Andrea G. Pigliafreddo
Andrea G. Pigliafreddo

Reputation: 347

mysql ranking on sum and best score

i have a table with this structure:

score_id | user_id | category | subcategory | score
1        | 1       | game     | 0           | 100
2        | 1       | game     | 0           | 200
3        | 1       | quiz     | 0           | 2000
4        | 1       | quiz     | 1           | 1000
5        | 1       | game     | 0           | 10
6        | 1       | game     | 1           | 10
7        | 1       | game     | 2           | 100
8        | 1       | game     | 1           | 500
9        | 2       | game     | 0           | 600

and i need different query mysql:

1) ranking of all team grouped by user_id sum of all best result beetween all records of user for category and subcategory

expected result

user_id | total_score | ranking
1       | 3310        | 1
2       | 600         | 2

where

3310 = (200 + 10 + 100+1000+2000) 
 200 is the best result of game 0, 
 10 is the best result of game 1, 
 100 is the best result of game 2, 
 2000 is the best result of quiz 0
 1000 is the best result of quiz 1

600 = (600)
 600 is the best result of game 0,

SOLUTION by @Strawberry (thank you)

SELECT a.*
     , @i:=@i+1 rank
  FROM 
     ( SELECT user_id
            , SUM(subtotal) total 
         FROM 
           ( SELECT user_id
                   , category
                   , subcategory
                   , MAX(score) subtotal 
                FROM my_table 
               GROUP 
                  BY user_id
                   , category
                   , subcategory
            ) x 
        GROUP 
           BY user_id
     ) a
  JOIN 
     ( SELECT @i:=0) vars
 ORDER
    BY total DESC;

2) ranking of all team grouped by user_id sum of all best result beetween all records of user for category and subcategory for only game or quiz

expected result game

user_id | total_score | ranking
2       | 600         | 1
1       | 310         | 2

where 

600 = (600)
 600 is the best result of game 0, 

310 = (200 + 10 + 100) 
 200 is the best result of game 0, 
 10 is the best result of game 1, 
 100 is the best result of game 2,

expected result quiz

user_id | total_score | ranking
1       | 3000       | 1
2       | 0          | 2

where 

3000 = (200 + 10 + 100) 
 2000 is the best result of quiz 0
 1000 is the best result of quiz 1

 0= (0)
(user_id= 2 don't play quiz)

BASED ON SOLUTION by @Strawberry (thank you)

SELECT a.*
     , @i:=@i+1 rank
  FROM 
     ( SELECT user_id
            , SUM(subtotal) total 
         FROM 
           ( SELECT user_id
                   , category
                   , subcategory
                   , MAX(score) subtotal 
                FROM my_table
                WHERE category = 'game' // or 'quiz
               GROUP 
                  BY user_id
                   , category
                   , subcategory
            ) x 
        GROUP 
           BY user_id
     ) a
  JOIN 
     ( SELECT @i:=0) vars
 ORDER
    BY total DESC;

3) ranking of all team grouped by user_id sum of all best result beetween all records of user for category and subcategory for only game or quiz with specific subcategory

expected result game 0

user_id | total_score | ranking
2       | 600         | 1
1       | 310         | 2

where 

600 = (600)
 600 is the best result of game 0, 

200 = (200 ) 
 200 is the best result of game 0,

BASED ON SOLUTION by @Strawberry (thank you)

SELECT a.*
     , @i:=@i+1 rank
  FROM 
     ( SELECT user_id
            , SUM(subtotal) total 
         FROM 
           ( SELECT user_id
                   , category
                   , subcategory
                   , MAX(score) subtotal 
                FROM my_table
                WHERE category = 'game'
                AND subcategory = '0'
               GROUP 
                  BY user_id
                   , category
                   , subcategory
            ) x 
        GROUP 
           BY user_id
     ) a
  JOIN 
     ( SELECT @i:=0) vars
 ORDER
    BY total DESC;

4) get total score of single user (ex user_id=1 ) for query 1

5) get ranking of single user (ex user_id=1 ) for query 1

6) get total score of single user (ex user_id=1 ) for query 2

7) get ranking of single user (ex user_id=1 ) for query 2

8) get total score of single user (ex user_id=1 ) for query 3

9) get ranking of single user (ex user_id=1 ) for query 3

thank you!

Upvotes: 0

Views: 469

Answers (1)

Strawberry
Strawberry

Reputation: 33945

Here's the first one. Using this, show us your best efforts for the remainder...

SELECT a.*
     , @i:=@i+1 rank
  FROM 
     ( SELECT user_id
            , SUM(subtotal) total 
         FROM 
            ( SELECT user_id
                   , category
                   , subcategory
                   , MAX(score) subtotal 
                FROM my_table 
               GROUP 
                  BY user_id
                   , category
                   , subcategory
            ) x 
        GROUP 
           BY user_id
     ) a
  JOIN 
     ( SELECT @i:=0) vars
 ORDER
    BY total DESC;

Note that this solution doesn't account for ties.

Upvotes: 1

Related Questions