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 | +————+————-+————+————————-+