old_blueyes Posted June 21, 2015 Share Posted June 21, 2015 Hi, i have my query 95% working, it returns a list of users ordered by total score SELECT firstname, surname, SUM(score) AS total, score AS gameweek FROM users INNER JOIN points ON users.userID = points.userID GROUP BY users.userID ORDER BY total DESC however I wish to display a current weekly score (gameweek), separately from the overall score I thought I could get it to work by querying the MAX week and displaying the score but so far i'm having no luck. my tables are as follows: USERS userID | username | password | firstname | surname POINTS id | userID | week | score 1 | 1 | 1 | 4 2 | 2 | 1 | 1 3 | 3 | 1 | 3 4 | 1 | 2 | 0 5 | 2 | 2 | 4 6 | 3 | 2 | 1 Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted June 22, 2015 Share Posted June 22, 2015 (edited) Try, SELECT u.firstname, u.surname, a.userID, max_week, gameweek FROM POINTS a inner join (select userID, max(week) as max_week, score as gameweek from POINTS group by userID) as b ON a.userID = b.userID and b.max_week = a.week inner join USERS u ON u.userID = b.userID Edited June 22, 2015 by jazzman1 Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted June 22, 2015 Share Posted June 22, 2015 (edited) Barand will kill me if he sees my previous reply. Try next (already tested) select u.firstname, u.surname, a.userID, X.week as 'Max Week', X.score as 'Gameweek' from POINTS a inner join (select userID, week, score from POINTS a1 where week = (select max(week) from POINTS a2 where a2.userID = a1.userID) group by userID) as X ON X.userID = a.userID and X.week = a.week inner join USERS u ON u.userID = X.userID I needed to use a subquery after the where clause or you could use another inner join inside the first join table instead! Edited June 22, 2015 by jazzman1 Quote Link to comment Share on other sites More sharing options...
Barand Posted June 22, 2015 Share Posted June 22, 2015 or SELECT firstname , surname , week AS gameweek , SUM(score) AS total FROM points INNER JOIN users ON users.userID = points.userID WHERE week = (SELECT MAX(week) FROM points) GROUP BY points.userID ORDER BY total DESC Quote Link to comment Share on other sites More sharing options...
old_blueyes Posted June 22, 2015 Author Share Posted June 22, 2015 Thanks for the proposed solutions above, I haven't had chance to test them yet as i'm at work. But on first look I don't think this solution will give me an overall total (cumulative total of all weeks score) as well as weekly total (single score value taken from max week no) I think it's my own fault for not explaining it properly, but what I had in mind for the final output table was: firstname surname | weekly total | overall total Barand will kill me if he sees my previous reply. Try next (already tested) select u.firstname, u.surname, a.userID, X.week as 'Max Week', X.score as 'Gameweek' from POINTS a inner join (select userID, week, score from POINTS a1 where week = (select max(week) from POINTS a2 where a2.userID = a1.userID) group by userID) as X ON X.userID = a.userID and X.week = a.week inner join USERS u ON u.userID = X.userID I needed to use a subquery after the where clause or you could use another inner join inside the first join table instead! Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted June 22, 2015 Solution Share Posted June 22, 2015 perhaps SELECT firstname , surname , week AS gameweek , SUM(score) AS total , tot.overall FROM points INNER JOIN users ON users.userID = points.userID INNER JOIN ( SELECT userID , SUM(score) as overall FROM points GROUP BY userID ) as tot ON points.userID = tot.userID WHERE week = (SELECT MAX(week) FROM points) GROUP BY points.userID ORDER BY total DESC Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.