AVG

Calculates the average of the values in expr that are not NULL. If all values are NULL, NULL is returned.

Syntax

As an Aggregate Function

AVG([DISTINCT] expr)

As a Window Function

AVG([DISTINCT] expr) OVER (
                            [PARTITION BY part]
                            [ORDER BY ord [ASC|DESC] [window_frame]]
                            )

Arguments

expr: Numeric value (INTEGER or DECIMAL). If the value is not numeric, an attempt is made to convert it to a numeric value. If conversion is not possible, an error is returned.

---

part (optional): Expression to partition by.

---

ord (optional): Optional expression to order within each partition.

---

window_frame (cumulative or sliding): Defines the subset of rows within the window for which values are returned. See also Working with window functions.

Usage Notes

If expr is not numeric, an attempt is made to implicitly convert expr to a floating-point value. If conversion is not possible, an error is returned.

Example 1 | With 'DISTINCT' Clause

select  avg(col1), 
        avg(col2),
        avg(DISTINCT col1), 
        avg(DISTINCT col2)              
             from  (select 'a' as ord1, 'g1' as grp1, '10' as col1, 10 as col2
                    union all 
                    select 'b' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all
                    select 'c' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all 
                    select 'a' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'b' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'c' as ord1, 'g2' as grp1, '40' as col1, 40 as col2
                    union all 
                    select 'a' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'b' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'c' as ord1, 'g3' as grp1, '60' as col1, 60 as col2
                    union all
                    select 'd' as ord1, 'g3' as grp1, '50' as col1, 50 as col2);

+----------+------------------+--------------------+--------------------+
| AVG(COL1)| AVG(MY2)         | AVG(DISTINCT COL1) | AVG(DISTINCT COL2) |
+----------+------------------+--------------------+--------------------+
| 36       | 33.33            | 35                 | 36                 |
+----------+------------------+--------------------+--------------------+

Example 2 | Window Function

select  avg(col1) OVER(PARTITION BY grp1) as result1,
        avg(col1) OVER(PARTITION BY grp1 ORDER BY ord1 ASC) as result2,
        avg(col1) OVER(PARTITION BY grp1 ORDER BY ord1 DESC) as result3,
        avg(col1) OVER(PARTITION BY grp1 ORDER BY ord1 DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as result3_move_avg
            from  (select 'a' as ord1, 'g1' as grp1, 10 as col1, 10 as col2
                    union all 
                    select 'b' as ord1, 'g1' as grp1, 20 as col1, 20 as col2
                    union all
                    select 'c' as ord1, 'g1' as grp1, 20 as col1, 20 as col2
                    union all 
                    select 'a' as ord1, 'g2' as grp1, 30 as col1, NULL as col2 
                    union all 
                    select 'b' as ord1, 'g2' as grp1, 30 as col1, NULL as col2 
                    union all 
                    select 'c' as ord1, 'g2' as grp1, 40 as col1, 40 as col2
                    union all 
                    select 'a' as ord1, 'g3' as grp1, 50 as col1, NULL as col2
                    union all 
                    select 'b' as ord1, 'g3' as grp1, 50 as col1, NULL as col2
                    union all 
                    select 'c' as ord1, 'g3' as grp1, 60 as col1, 60 as col2
                    union all
                    select 'd' as ord1, 'g3' as grp1, 50 as col1, 50 as col2);

+---------+---------+---------+--------------------+
| RESULT1 | RESULT2 | RESULT3 | RESULT3_MOVE_AVG   |
+---------+---------+---------+--------------------+
| 16.67   | 10      | 16.67   | 15                 |
| 16.67   | 15      | 20      | 16.67              |
| 16.67   | 16.67   | 20      | 20                 |
| 33.33   | 30      | 33.33   | 30                 |
| 33.33   | 30      | 35      | 33.33              |
| 33.33   | 33.33   | 40      | 35                 |
| 52.5    | 50      | 52.5    | 50                 |
| 52.5    | 50      | 53.33   | 53.33              |
| 52.5    | 53.33   | 55      | 53.33              |
| 52.5    | 52.5    | 50      | 55                 |
+---------+---------+---------+--------------------+