Categories:

Aggregate Functions (General) , Window Functions (General, Window Frame)

AVG

Returns the average of non-NULL records. If all records inside a group are NULL, the function returns NULL.

Syntax

Aggregate function

AVG( [ DISTINCT ] <expr1> )

Window function

AVG( <expr1> ) OVER ( [ PARTITION BY <expr2> ] [ ORDER BY <expr3> [ ASC | DESC ] [ <window_frame> ] ] )

For details about window_frame syntax, see Window Frame Syntax and Usage.

Arguments

expr1

This is an expression that evaluates to a numeric data type (INTEGER, FLOAT, DECIMAL, etc.).

expr2

This is the optional expression to partition by.

expr3

This is the optional expression to order by within each partition.

Usage Notes

  • AVG on a STRING expression results in an implicit cast of the input to floating point values. If the cast cannot be performed, an error is returned.

  • When called as a window function:

    • The keyword DISTINCT is permitted syntactically, but is ignored.

    • If an ORDER BY sub-clause is used inside the OVER clause, then a window frame must be used. If no window frame is specified, then the default is a cumulative window frame:

      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

      For more details about window frames, including syntax and examples, see Window Frame Syntax and Usage.

      For more information about implied window frames, see Window Frame Usage Notes.

Examples

Setup:

CREATE OR REPLACE TABLE avg_example(int_col int, d decimal(10,5), s1 varchar(10), s2 varchar(10));
INSERT INTO avg_example VALUES
    (1, 1.1, '1.1','one'), 
    (1, 10, '10','ten'),
    (2, 2.4, '2.4','two'), 
    (2, NULL, NULL, 'NULL'),
    (3, NULL, NULL, 'NULL'),
    (NULL, 9.9, '9.9','nine');

Show the data:

SELECT * 
    FROM avg_example 
    ORDER BY int_col, d;
+---------+----------+------+------+
| INT_COL |        D | S1   | S2   |
|---------+----------+------+------|
|       1 |  1.10000 | 1.1  | one  |
|       1 | 10.00000 | 10   | ten  |
|       2 |  2.40000 | 2.4  | two  |
|       2 |     NULL | NULL | NULL |
|       3 |     NULL | NULL | NULL |
|    NULL |  9.90000 | 9.9  | nine |
+---------+----------+------+------+

Calculate the average of the columns that are numeric or that can be converted to numbers:

SELECT AVG(int_col), AVG(d)
    FROM avg_example;
+--------------+---------------+
| AVG(INT_COL) |        AVG(D) |
|--------------+---------------|
|     1.800000 | 5.85000000000 |
+--------------+---------------+

Combine AVG with GROUP BY to calculate the averages of different groups:

SELECT int_col, AVG(d), AVG(s1) 
    FROM avg_example 
    GROUP BY int_col
    ORDER BY int_col;
+---------+---------------+---------+
| INT_COL |        AVG(D) | AVG(S1) |
|---------+---------------+---------|
|       1 | 5.55000000000 |    5.55 |
|       2 | 2.40000000000 |    2.4  |
|       3 |          NULL |    NULL |
|    NULL | 9.90000000000 |    9.9  |
+---------+---------------+---------+

Use as a simple window function:

SELECT 
       int_col,
       AVG(int_col) OVER(PARTITION BY int_col) 
    FROM avg_example
    ORDER BY int_col;
+---------+-----------------------------------------+
| INT_COL | AVG(INT_COL) OVER(PARTITION BY INT_COL) |
|---------+-----------------------------------------|
|       1 |                                   1.000 |
|       1 |                                   1.000 |
|       2 |                                   2.000 |
|       2 |                                   2.000 |
|       3 |                                   3.000 |
|    NULL |                                    NULL |
+---------+-----------------------------------------+