Retrieving Aggregate Column Information

Go Up to Listing Columns to Retrieve with SELECT

SELECT can include aggregate functions, functions that calculate or retrieve a single, collective numeric value for a column or expression based on each qualifying row in a query rather than retrieving each value separately. The following table lists the aggregate functions supported by InterBase:

Aggregate functions in SQL

Function	Purpose
AVG()	Calculates the average numeric value for a set of values.
MIN()	Retrieves the minimum value in a set of values.
MAX()	Retrieves the maximum value in a set of values.
SUM()	Calculates the total of numeric values in a set of values.
COUNT()	Calculates the number of rows that satisfy the query’s search condition (specified in the WHERE clause).

For example, the following query returns the average salary for all employees in the EMPLOYEE table:

EXEC SQL
SELECT AVG(SALARY)
INTO :avg_sal
FROM EMPLOYEE;

The following SELECT returns the number of qualifying rows it encounters in the EMPLOYEE table, both the maximum and minimum employee number of employees in the table, and the total salary of all employees in the table:

EXEC SQL
SELECT COUNT(*), MAX(EMP_NO), MIN(EMP_NO), SUM(SALARY)
INTO :counter, :maxno, :minno, :total_salary
FROM EMPLOYEE;

If a field value involved in an aggregate calculation is NULL or unknown, the entire row is automatically excluded from the calculation. Automatic exclusion prevents averages from being skewed by meaningless data.

Note: Aggregate functions can also be used to calculate values for groups of rows. The resulting value is called a group aggregate. For more information about using group aggregates, see Grouping Rows with GROUP BY.