AVG
Returns the average value within the requested window.
For additional window functions, see Window Functions.
Syntax
AVG( <val> ) OVER ( [ PARTITION BY <exp> ] )
Parameter | Description |
---|---|
<val> | An expression used for the AVG() function. |
<expr> | An expression used for the partition by clause. |
Example
The example below is querying test scores for students in various grade levels. Unlike a regular AVG()
aggregation, the window function allows us to see how each student individually compares to the average test score for their grade level.
SELECT
first_name,
grade_level,
test_score,
AVG(test_score) OVER (PARTITION BY grade_level) AS average_for_grade
FROM
class_test;
Returns:
| first_name | grade_level | test_score | average_for_grade | | :———-| :———–| :————|:————————-| | Frank | 9 | 76 | 81.33333333333333 | | Humphrey | 9 | 90 | 81.33333333333333 | | Iris | 9 | 79 | 81.33333333333333 | | Sammy | 9 | 85 | 81.33333333333333 | | Peter | 9 | 80 | 81.33333333333333 | | Jojo | 9 | 78 | 81.33333333333333 | | Brunhilda | 12 | 92 | 89 | | Franco | 12 | 94 | 89 | | Thomas | 12 | 66 | 89 | | Gary | 12 | 100 | 89 | | Charles | 12 | 93 | 89 | | Jesse | 12 | 89 | 89 | | Roseanna | 11 | 94 | 73 | | Carol | 11 | 52 | 73 | | Wanda | 11 | 73 | 73 | | Shangxiu | 11 | 76 | 73 | | Larry | 11 | 68 | 73 | | Otis | 11 | 75 | 73 | | Deborah | 10 | 78 | 68.2 | | Yolinda | 10 | 30 | 68.2 | | Albert | 10 | 59 | 68.2 | | Mary | 10 | 85 | 68.2 | | Shawn | 10 | 89 | 68.2 | +————+————-+————+————————-+