Aggregate Functions

Using Aggregate Functions › Aggregate Functions

Aggregate Functions

There are five aggregate functions. Except for COUNT, these functions operate on a collection of values in one column of a table and produce a single result:

Function	Meaning
AVG	Returns the average of all values in the named column
COUNT	Counts the number of rows that satisfy a condition
MAX	Returns the highest value in the named column
MIN	Returns the lowest value in the named column
SUM	Returns the total of all values in the named column

How to Use Aggregate Functions

To use an aggregate function, you specify the name of the function followed by the name of the column in parentheses, as in SUM(SALARY_AMOUNT).

You can use the aggregate functions AVG, MAX, MIN, and SUM with a column name (SALARY_AMOUNT) or, if you are using &U$IDBGJ., you can use these functions with an arithmetic expression (SALARY_AMOUNT/52) as well.

Where to Use Aggregate Functions

Instead of a column name with SELECT
In a HAVING clause as a value in a predicate

AVG

The aggregate function AVG calculates the average value of all rows in a specified column.

How It's Done

The president of Commonwealth Auto wants to know the average salary for all employees in the company. To produce this information, use the POSITION table and enter:

select avg(salary_amount)
       from position;

The result looks like this:

(EXPR) ------ 51101.16 1 row processed