AVG Function

Description

The AVG function calculates the arithmetic average of the value of an expression representing all rows. Only one expression is specified as a parameter. You can get the average without duplicates by using the DISTINCT or UNIQUE keyword in front of the expression or the average of all values by omitting the keyword or by using ALL.

For analytic function examples, see the following analytic function examples. For more information, see Overview.

Syntax

AVG ( [ { DISTINCT | DISTINCTROW } | UNIQUE | ALL ] expression )

• expression: Specifies an expression that returns a numeric value. An expression that returns a collection-type data is not allowed.
• ALL: Calculates an average value for all data (default).
• DISTINCT or UNIQUE: Calculates an average value without duplicates.

Example of Aggregate Function

The following example shows how to retrieve the average number of gold (gold) medals that Korea won in Olympics in the demodb database.

SELECT AVG(gold)

FROM participant

WHERE nation_code = 'KOR'; 

                 avg(gold)

==========================

     9.600000000000000e+00

Example of Analytic Function

The following example shows the average number of gold medals that Korean wons in the demodb.

SELECT AVG(gold)

FROM participant

WHERE nation_code = 'KOR'; 

                 avg(gold)

==========================

     9.600000000000000e+00

Example of Analytic Function

The following example shows how to output the number of gold medals by year and the maximum number of gold medals in history, acquired by the country whose nation_code code starts with 'AU'.

SELECT host_year, nation_code, gold,

MIN(gold) OVER (PARTITION BY nation_code) mn_gold

FROM participant WHERE nation_code like 'AU%' ORDER BY nation_code, host_year;

    host_year  nation_code                  gold      mx_gold

=============================================================

         1988  'AUS'                           3           17

         1992  'AUS'                           7           17

         1996  'AUS'                           9           17

         2000  'AUS'                          16           17

         2004  'AUS'                          17           17

         1988  'AUT'                           1            2

         1992  'AUT'                           0            2

         1996  'AUT'                           0            2

         2000  'AUT'                           2            2

         2004  'AUT'                           2            2

The following example is removing the "ORDER BY host_year" clause under the OVER analysis clause from the above example. The avg_gold value is the average of gold medals for all years, so the value is identical for every year by nation_code.

SELECT host_year, nation_code, gold, AVG(gold) OVER (PARTITION BY nation_code) avg_gold

FROM participant WHERE nation_code LIKE 'AU%';

    host_year  nation_code                  gold                  avg_gold

==========================================================================

         2004  'AUS'                          17     1.040000000000000e+01

         2000  'AUS'                          16     1.040000000000000e+01

         1996  'AUS'                           9     1.040000000000000e+01

         1992  'AUS'                           7     1.040000000000000e+01

         1988  'AUS'                           3     1.040000000000000e+01

         2004  'AUT'                           2     1.000000000000000e+00

         2000  'AUT'                           2     1.000000000000000e+00

         1996  'AUT'                           0     1.000000000000000e+00

         1992  'AUT'                           0     1.000000000000000e+00

         1988  'AUT'                           1     1.000000000000000e+00