-1
+------------+--------+-----------+---------------+
|  paydate   | salary | ninumber  | payrollnumber |
+------------+--------+-----------+---------------+
| 2015-05-15 |   1000 | jh330954b |             6 |
| 2015-04-15 |   1250 | jh330954b |             5 |
| 2015-03-15 |    800 | jh330954b |             4 |
| 2015-02-15 |    894 | jh330954b |             3 |
| 2015-05-15 |    500 | ew56780e  |             6 |
| 2015-04-15 |   1500 | ew56780e  |             5 |
| 2015-03-15 |   2500 | ew56780e  |             4 |
| 2015-02-15 |   3000 | ew56780e  |             3 |
| 2015-05-15 |    400 | rt321298z |             6 |
| 2015-04-15 |    582 | rt321298z |             5 |
| 2015-03-15 |    123 | rt321298z |             4 |
| 2015-02-15 |    659 | rt321298z |             3 |
+------------+--------+-----------+---------------+

The above list is the data in my database. I need to get the average of the previous 3 salaries for each individual and output this.

I don't know where to begin with this so I cannot provide any of my working so far.

shA.t
  • 16,580
  • 5
  • 54
  • 111
wallbanged
  • 17
  • 4
  • 1
    https://stackoverflow.com/questions/26618353/t-sql-calculate-moving-average – MatthewMartin May 15 '15 at 18:18
  • Welcome to stackoverflow. Please read [ask]. – Zohar Peled May 15 '15 at 18:22
  • It is helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. – HABO May 16 '15 at 17:04

1 Answers1

5

In SQL Server, you can use row_number() to get the last three salaries in a subquery. Then use avg():

select ninumber, avg(salary)
from (select t.*,
             row_number() over (partition by ninumber order by payrollnumber desc) as seqnum
      from table t
     ) t
where seqnum <= 3
group by ninumber;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786