Get Last 5 matches out of Fixtures table

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.

Well this is the idea:



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.

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

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:


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 :


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

will this also run in MS - Access?

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;

Thanks! you're the best!