get score based on max week no

[old_blueyes]

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

 

[jazzman1]

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

[jazzman1]

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

[Barand]

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

[old_blueyes]

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!

[Barand]

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