Highest three averages

Database

Highest three averages

I have a small database to keep track of weekly scores.  Each participant can have 2 or more scores each week.  Some players don't play every week and have no scores for those weeks.  I have the following tables:

Person:  pid, pname

Week: wn, CalDate

Scores:  wn, pid, score

The scores table always has at least two scores [ rows ] for each player with any scores for that week, but not all players have scores every week.

I need to get the weekly average of the most recent three weeks for each player and then get the average of those three averages.  Since some players don't play each week, an average for one player may come from weeks 4, 8, and 9, while another player has averages from weeks 8, 9, and 10.

I have a query that gets the averages of all  the players for each week, and another query that gets the averages of those averages, but I haven't found a way to limit the first query to only the most recent three weeks.

I think the "top" function might help, but I've never used that before.  Any help would be appreciated.

Tags (3)
5 REPLIES
Senior Apprentice

Re: Highest three averages

Hi Jack,

you get the three most recent weeks using OLAP functions:

select wn, pid, avg(score) as avgscore 
from scores
group by wn, pid
qualify rank() over (partition by pid order by wn desc) <= 3

Re: Highest three averages

Thanks for the fast response.  I still have a problem, though.  When I run this query, I get this message:

Syntax error (missing operator) in query expriession 'pid

qualify rank() over (partition by pid order by wn desc) <= 3'

Do I need a comma somewhere?

Senior Apprentice

Re: Highest three averages

Hi Jack,

the Select is syntactically valid, can you show your SQL and the exact error message?

Which client do you use?

Re: Highest three averages

I used your exact statement.  However, if I omit the last line, the query returns data without error, but it includes all the weeks, not just the most recent three.

  I am running access version 14.0.4730.1000.  Since this is a "single-user" database, I would think I don't need a more robust db engine.

Senior Apprentice

Re: Highest three averages

You're using Access as database? Ouch.

Well, this is a Teradata forum and answers are based on Teradata's SQL dialect. 

Access doesn't support QUALIFY or any OLAP function. 

Using the limited SQL Access supports the only way to get that info is using a cursor sorted by pid and descending wn.