Get Last 5 matches out of Fixtures table

Hi,

I have a table called "GamesTable"

Columns are ID, Date, PlayerName, GamesWon,GamesLost,GamePlayed( this has always value  1)

Now I want to make a query to get the average of games won/Lost and the sum of Games Played for every player BUT in his last 5 matches. I have no idea how I can get the last 5 matches for each and every player out of the GamesTable.

Can you help me with this?
Gerben69Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mankowitzCommented:
if the games are sorted by date, you can use that, right?

I'm not sure you have enough information in your table. Does each row of your table refer to a particular match? a particular player?

What are the GamesWon and GamesLost columns? Are they a running tally?

Please post some sample data and your expected result so we can help more.
0
Gerben69Author Commented:
Well this is the idea:

db.png

Above you see a piece of my db.

PlayerName is the name of a tennis player. GamesWon is the number of games won in his particular match on that date. GamesLost is the opposit. I will also add a column GamePlayed. This will have the value 1 if the match is completed

But now I want to be able to make a userform when choosing for a player , I get his avg GamesWon and Lost over his last 5 matches played. And the sum of GamePlayed over his last 5 matches. Because not every player will be in the list 5 or  more times.

So I need a query to calculate the AVG of GamesWon, GamesLost and the SUM of GamePlayed for every player in his last 5 matches.
0
mankowitzCommented:
OK, so I just want to make sure
1. A match includes any number of games played on a single calendar day.
2. When you want to know the result of the last 5 matches, you are looking for the total number of matches in which gameswon is greater than gameslost

You want something like the following. I don't have sqlserver to test right now, but try this

create table test.dbo.gamesplayed (id int, playername varchar(25), dateplayed date, gameswon int, gameslost int);
insert gamesplayed (id, playername, dateplayed, gameswon, gameslost) values
(1, 'a', '3/1/2015', 2, 5),
(2, 'a', '3/2/2015', 10, 5),
(3, 'a', '3/3/2015', 2, 15),
(4, 'a', '3/4/2015', 2, 8),
(5, 'a', '3/5/2015', 8, 5),
(6, 'a', '3/6/2015', 4, 5),
(7, 'b', '3/7/2015', 2, 8),
(8, 'b', '3/8/2015', 2, 0),
(9, 'b', '3/9/2015', 4, 5),
(10, 'c', '3/10/2015', 9, 5),
(11, 'c', '3/11/2015', 2, 4),
(12, 'c', '3/12/2015', 2, 5);

 SELECT
        max(gp.playername),  
        sum(case when gameswon>gameslost then 1 else 0 end) Wins  ,
        count(*) Played      
    FROM test.dbo.gamesplayed gp
    WHERE id IN
    (
        SELECT TOP 5 id
        from test.dbo.gamesplayed        
        where playername=gp.playername
        ORDER BY dateplayed desc
    )
    group by gp.playername
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Gerben69Author Commented:
Not exactly.

My aim is just to retrieve the last 5 matches of every player and get the avg of Games Won , Games Lost and the sum of Game played.

So for example:

01/01/2015 - Federer R. - 12 - 3 - 1
02/01/2015 - Federer R. - 12 - 7 - 1
03/01/2015 - Federer R. - 13 - 10 - 1
04/01/2015 - Federer R. - 2 - 12 - 1
05/01/2015 - Federer R. - 12 - 3 - 1
06/01/2015 - Federer R. - 12 - 8 - 1
07/01/2015 - Federer R. - 12 - 4 - 1

The sql query should than look for Federer'd last 5 matches and calculate the avg of his Games Won and Lost and the sum of game Played

So in this example :

03/01/2015 - Federer R. - 13 - 10 - 1
04/01/2015 - Federer R. - 2 - 12 - 1
05/01/2015 - Federer R. - 12 - 3 - 1
06/01/2015 - Federer R. - 12 - 8 - 1
07/01/2015 - Federer R. - 12 - 4 - 1

Avg(GamesWon) = (13+2+12+12+12)/5 = 51/5= 10,...
Avg(GamesLost) = (10+12+3+8+4)/5 = 37/5 = 7,...
Sum(GamePlayed) = 1+1+1+1+1 = 5
0
mankowitzCommented:
ok, that's easier

 SELECT
        max(gp.playername),  AVG(gameswon) Won, AVG(gameslost) Lost,
        count(*) Played      
    FROM gamesplayed gp
    WHERE id IN
    (
        SELECT TOP 5 id
        from gamesplayed        
        where playername=gp.playername
        ORDER BY [Date] desc
    )
    group by gp.playername
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gerben69Author Commented:
will this also run in MS - Access?
0
mankowitzCommented:
In access, try this:
SELECT Max(gp.playername) AS MaxOfplayername, Avg(gp.gameswon) AS AvgOfgameswon, Avg(gp.gameslost) AS AvgOfgameslost, Count(*) AS Played
FROM gamesplayed AS gp
WHERE (((gp.[id]) In (SELECT TOP 5 id
        from gamesplayed        
        where playername=gp.playername
        ORDER BY dateplayed desc
    )))
GROUP BY gp.playername;
0
Gerben69Author Commented:
Thanks! you're the best!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Miscellaneous

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.