CODEWITHSUNDEEP
sqlsql-server
user11333092
user11333092

Reputation:

How to calculate a group average in SQL?

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?

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you want a window function:

select t.*,
       avg(result * 1.0) over (partition by name) as result
from t;

Upvotes: 4

Related Questions