I have a sql data set which look something like:
Name | Result | Year | John | 9 | 2017 | John | 5 | 2018 | John | 12 | 2019 | Sara | 7 | 2017 | Sara | 9 | 2018 | Sara | 18 | 2019 | Peter | 9 | 2017 | Peter | 20 | 2018 | Peter | 2 | 2019 | Rebecca | 2 | 2017 | Rebecca | 21 | 2017 | Rebecca | 6 | 2017 |
I would like to be able to select the data in such a way that the query will return:
Name | Result | Year | Average | John | 9 | 2017 | 8.6 | John | 5 | 2018 | 8.6 | John | 12 | 2019 | 8.6 | Sara | 7 | 2017 | 11.3 | Sara | 9 | 2018 | 11.3 | Sara | 18 | 2019 | 11.3 | Peter | 9 | 2017 | 10.3 | Peter | 20 | 2018 | 10.3 | Peter | 2 | 2019 | 10.3 | Rebecca | 2 | 2017 | 9.7 | Rebecca | 21 | 2017 | 9.7 | Rebecca | 6 | 2017 | 9.7 |
With the average result calculated over the 3 years shown for each person.
How could I achieve this in SQL Server?
Advertisement
Answer
I think you want a window function:
select t.*,
avg(result * 1.0) over (partition by name) as result
from t;