Top 3 scores average - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Top 3 scores average

Author

Topic

will0122
Starting Member

11 Posts

Posted - 2009-07-06 : 16:29:25

Hi,
I am a SQL beginner and I am trying to do the following but to be honest I am kind of lost. I have two tables and the employee id is the field that joins the two tables, in one table I have the list of employees and in the other one I have their scores sorted by ID and Date of the score. I need to create a query that gives me the average for each employee taking in consideration the last 3 scores only. Thank you very much in advanced.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 16:32:52

Here is an excellent starter for you. This is WHY
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

And this is HOW
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

N 56°04'39.26"
E 12°55'05.63"

will0122
Starting Member

11 Posts

Posted - 2009-07-06 : 16:58:19

I have
Table 1.
1, RCAF
2, Name
3, Group

Table 2
1, RCAF
2, Score
3, Date

Sample Data Table 1
A8CE1 Cruz STOLI
A8AR30 Abigail STOLI

Sample Data Table 2
a8ar30 13 2009-06-30 14:15:00.000
a8ar30 14 2009-06-25 10:35:00.000
a8ar30 14 2009-06-22 16:55:00.000
a8ar30 12 2009-06-15 09:05:00.000
a8ar30 14 2009-06-08 17:19:00.000
a8ce1 11 2009-06-30 11:30:00.000
a8ce1 13 2009-06-24 18:19:00.000
a8ce1 12 2009-06-22 10:41:00.000
a8ce1 12 2009-06-11 14:51:00.000
a8ce1 14 2009-06-08 10:51:00.000

Expected Result
RCAF AVG last 3 Scores
a8ar30 13.6666
a8ce1 12
The result is the average of the last 3 scores

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-06 : 17:00:04

Are you using SQL server 2005 or higher?

EDIT : Also why do you need the first table, if you are not using any fields from that one

will0122
Starting Member

11 Posts

Posted - 2009-07-06 : 17:02:47

Yes I am using SQL Server 2005; I thought I might need it as a reference to get each average from that list.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-06 : 17:07:56

Try this

SELECT   a.rcaf, 
         avg(a.score * 1.0) AS [Average] 
FROM     (SELECT rcaf, 
                 score, 
                 [date], 
                 Row_number() 
                   OVER(PARTITION BY rcaf ORDER BY [date] DESC) AS seq 
          FROM   table2) a 
WHERE    a.seq <= 3 
GROUP BY a.rcaf

EDIT : changed it to use avg function

will0122
Starting Member

11 Posts

Posted - 2009-07-06 : 17:24:48

Awesome
I just did few modifications to fit my tables and it work just fine. Thank you very much.
SELECT a.rcaf,
avg(a.score * 1.0) AS [Average]
FROM (SELECT rcaf,
score,
[ReviewdON],
Row_number()
OVER(PARTITION BY rcaf ORDER BY [ReviewdON] DESC) AS seq
FROM VerintInfo) a
WHERE a.seq <= 3
GROUP BY a.rcaf

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-06 : 17:35:50

welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-07 : 01:58:54

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

will0122
Starting Member

11 Posts

Posted - 2010-03-04 : 11:20:58

and by any chance guys would hoy happen to know how to do this but in MS access? thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 11:34:04

quote:
Originally posted by will0122

and by any chance guys would hoy happen to know how to do this but in MS access? thanks.

Try posting in access forum here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources