Multi-Row SQL Functions
Oracle Tips by Burleson

Multi-row SQL functions (also called group or aggregate functions) work with groups of rows. These functions ignore NULL values, except where noted in this section. The most commonly used multi-row SQL functions fall into the numeric group. All functions listed in this section are numeric functions.

avg()

The avg() function returns the average value of a numeric field from a group of rows. For example,

SELECT avg (base_salary)

FROM   EMPLOYEES;

returns the average salary of all employees.

count()

The count() function counts the number of rows in a group of rows. This function counts all rows in the group, including those for which a NULL value is present. There are two ways of calling count(), as follows:

SELECT count (*)

FROM   EMPLOYEE_HISTORY

WHERE  employee_number = 90213

AND    warning         = 'Y';

SELECT count (married)

FROM   EMPLOYEE_HISTORY

WHERE  employee_number = 90213

AND    warning         = 'Y';

The first example returns the total number of rows that match the query’s WHERE clause. The second example returns the total number of rows that have a non-NULL value in the specified column.

max()

The max() function returns the highest value of a specified column from a group of rows. For example,

SELECT max (base_salary)

FROM   EMPLOYEES;

returns the salary of the highest paid employee.

min()

The min() function returns the lowest value of a specified column from a group of rows. For example,

SELECT min (base_salary)

FROM   EMPLOYEES;

returns the salary of the lowest paid employee.

sum()

The sum() function returns the total of all values for a specified column in a group of rows. For example,

SELECT sum (vacation_days_used)

FROM   EMPLOYEES;

returns the total number of vacation days taken by employees this year.

PL/SQL Functions

PL/SQL provides two important error reporting functions that are not provided by SQL: SQLCODE() and SQLERRM().

SQLCODE()

The SQLCODE() function provides the number of the latest Oracle error that has occurred. Following is an example of calling the SQLCODE() function:

vErrorCode := SQLCODE;

SQLERRM()

The SQLERRM() function provides the complete text of the most recent Oracle error that has occurred, including the error number provide by the SQLCODE() function. Following is an example of calling the SQLERRM() function: