Want to execute one complex Mysql query

Question

I want to get the result set only by one query if possible i have table like that

    ------------------------------
    user_id | activity_id | score 
    ------------------------------
        1   |      1      |  100
    ------------------------------
        1   |      1      |  50
    ------------------------------
        1   |      2      |  20
    ------------------------------
        1   |      3      |  10
    ------------------------------
        1   |      3      |  50
    ------------------------------
        2   |      1      |  300
    ------------------------------
        3   |      3      |  10
    ------------------------------
        3   |      2      |  40
    ------------------------------

All what i need to select each user with sum of all of his high score in each activity. for example the result set should be like that:

    ------------------------------
    user_id | score 
    ------------------------------
        1   |  170
    ------------------------------
        2   |  300
    ------------------------------
        3   |  50
    ------------------------------

Already ask : http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — Fractaliste, Nov 30 '13 at 22:18
The user_id with the hightest score grouping by activity is the same that the content with the hightest version grouping by id — Fractaliste, Nov 30 '13 at 22:24

score 2 · Accepted Answer · answered Nov 30 '13 at 22:19

2

To get the highest score for each user/activity pair, you would use the following query:

select user_id, activity_id, max(score) as highscore
from t
group by user_id, activity_id;

To add these up for a given user, you would make this a subquery and do another aggregation:

select user_id, sum(highscore)
from (select user_id, activity_id, max(score) as highscore
      from t
      group by user_id, activity_id
     ) t
group by user_id;

answered Nov 30 '13 at 22:19

Gordon Linoff

1,242,037
58
646
786

what if i want to join with table user to get user name for example ? – user1272589 Nov 30 '13 at 22:48

Want to execute one complex Mysql query

1 Answers1