Categories:

Aggregate Functions (General) , Window Functions

AVG

Returns the average of non-NULL records. You can use the DISTINCT keyword to compute the average of unique non-null values. If all records inside a group are NULL, the function returns NULL.

Syntax

AVG( [ DISTINCT ] <expr1> ) [ OVER ( [ PARTITION BY <expr2> ] [ ORDER BY <expr3> [ <window_frame> ] ] ) ]

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. (This does not control the order of the entire query output.)

Usage Notes

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

  • The function can be called as a window function (i.e., by specifying an OVER clause) and the window can include an optional window_frame.

    The window_frame (either cumulative or sliding) specifies the subset of rows within the window for which the averaged values are returned. If no window_frame is specified, the default is the following cumulative window frame (in accordance with the ANSI standard for window functions):

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    For more details about window frames, including syntax and examples, see Window Frames.

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
    ORDER BY int_col, d;
+--------------+---------------+
| 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  |
+---------+---------------+---------+

Show what happens when you try to calculate the average of strings that can’t be converted to numeric values:

SELECT AVG(s2) 
    FROM avg_example;
100038 (22018): Numeric value 'one' is not recognized