AVG(EXPR)WHEN AVG(DISTINCT EXPR) IS INVOKED, THE DISTINCT VALUES OF...

3. AVG(expr)

When AVG(DISTINCT expr) is invoked, the distinct values of expr are summed and

divided by the number of unique occurrences of expr. AVG(ALL expr) and AVG(expr)

add the non-null values of expr for each row and divide the sum by the number of

non-null rows in the group. The expr parameter must be a numeric value. Consider

the queries:

Query 1: select avg(2) from employees;

Query 2: select avg(salary) from employees;

Query 3: select avg(distinct salary) from employees;

Query 4: select avg(commission_pct) from employees;

There are 107 rows in the EMPLOYEES table. Query 1 adds the number 2 across 107

rows and divides the total by the number of rows to return the number 2. Numeric

literals submitted to the AVG function are returned unchanged. Query 2 adds the

SALARY value for each row to obtain the total salary amount of 691400, which is

divided by the rows with non-null SALARY values (107) to return the average 6461.68224.

There are 57 unique salary values, which when added, yield a total of 397900. Dividing

397900 by 57 returns 6980.70175 as the average of the distinct salary values, which is

OCA/OCP Oracle Database 11g All-in-One Exam Guide

464

returned by the third query. Adding the non-null COMMISSION_PCT values produces

a total of 7.8. Dividing this by the employee records with non-null COMMISSION_PCT

values (35) yields 0.222857143, which is returned by query 4.

The MAX and MIN Functions

The MAX and MIN functions return the maximum (largest) and minimum (smallest)

expr value in a group. The MAX and MIN functions operate on NUMBER, DATE, CHAR,

and VARCHAR2 data types. They return a value of the same data type as their input

arguments, which are either the largest or smallest items in the group. When applied

to DATE items, MAX returns the latest date and MIN returns the earliest one. Character

strings are converted to numeric representations of their constituent characters based

on the NLS settings in the database. When the MIN function is applied to a group of

character strings, the word that appears first alphabetically is returned, while MAX

returns the word that would appear last. The MAX and MIN functions have this syntax:

MAX([DISTINCT|ALL] expr); MIN([DISTINCT|ALL] expr)

This syntax may be deconstructed into the following forms: