MySQL: Get difference between two values in one table (multiple userids)?

Question

I was trying to find a solution but did not succeed even if it seems simple. So this might be a newbie question...

I have a table userscores with 3 columns:

date          userid   points
2012-05-01    1        23
2012-06-01    1        34
2012-07-01    1        44
2012-05-01    2        78
2012-06-01    2        94
2012-07-01    2        99
2012-06-01    3         2
2012-07-01    3         9

Now I need to get the difference of the points between 2012-05-01 and 2012-06-01 for each user.

Users' points that are not existing (example userid 3) have to be calculated as 2 - 0... for this I guess I can use COALESCE(qa_points,0).

I read about combining two subqueries for the calculation but failed implementing it.

Any help appreciated.

PS: This is not working:

SELECT t1.userid, t1.points - t2.points AS mpoints FROM (
        SELECT userid,points FROM `userscores` 
        WHERE YEAR(date) = YEAR('2012-05-01') 
        AND MONTH(date) = MONTH('2012-05-01') ) 
        AS t1
    JOIN (
        SELECT userid,points FROM `userscores` 
        WHERE YEAR(date) = YEAR('2012-04-01') 
        AND MONTH(date) = MONTH('2012-04-01') ) 
        AS t2
    ORDER BY mpoints DESC, t1.userid DESC;

raina77ow · Accepted Answer · 2012-06-24T15:45:35.290

2

I suppose your query will look like this:

    SELECT ul.userid, 
           ul.points - COALESCE(uf.points, 0) AS points_difference
      FROM userscores ul 
 LEFT JOIN 
           (SELECT userid, points FROM userscores WHERE `date` = '2012-05-01') AS uf
        ON uf.userid = ul.userid
     WHERE ul.date = '2012-06-01'

LEFT JOIN is used because you told that there may be no records for this user/former date combination.

edited Jun 24 '12 at 15:45

answered Jun 24 '12 at 15:35

raina77ow

103,633
15
192
229

And here's a working [SQL fiddle](http://sqlfiddle.com/#!2/ba915/21) to play with. ) – raina77ow Jun 24 '12 at 15:49
seems to work, thank you! I will credit you in the plugin I'm currently writing for www.question2answer.org sqlfiddle? wow haven't known that. +1 thanks! – Avatar Jun 24 '12 at 15:55

score 0 · Answer 2 · answered Jun 24 '12 at 15:49

0

Use this query:

SELECT t1.userid, 
( t1.points - (case t2.date when '2012-05-01' then t2.points else 0 end)) 
AS mpoints FROM userscores as t1
INNER JOIN  userscores as t2
ON t1.date = '2012-06-01' AND t1.userid=t2.userid

answered Jun 24 '12 at 15:49

Somnath Muluk

55,015
38
216
226

Thanks for your answer but this returns multiple userids with different points. I need each userid unique. – Avatar Jun 24 '12 at 15:55

MySQL: Get difference between two values in one table (multiple userids)?

2 Answers2