Jump to content
old_blueyes

get score based on max week no

Recommended Posts

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

 

Share this post


Link to post
Share on other sites

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 by jazzman1

Share this post


Link to post
Share on other sites

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 by jazzman1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.