Aggregate functions

Aggregate functions return a single value from a set of records. You can use an aggregate function as part of a SELECT statement, with a field name (for example, AVG(SALARY)), or in combination with a column expression (for example, AVG(SALARY * 1.07)).

You can precede the column expression with the DISTINCT operator to eliminate duplicate values.

Example

Copy

COUNT (DISTINCT last_name)

In this example, only unique last name values are counted.

Aggregate function	Returns
`SUM`	The total of the values in a numeric field expression. For example, `SUM(SALARY)` returns the sum of all salary field values.
`AVG`	The average of the values in a numeric field expression. For example, `AVG(SALARY)` returns the average of all salary field values.
`COUNT`	The number of values in any field expression. For example, `COUNT(NAME)` returns the number of name values. When using `COUNT` with a field name, `COUNT` returns the number of non-null field values. A special example is `COUNT(*)`, which returns the number of records in the set, including records with null values.
`MAX`	The maximum value in any field expression. For example, `MAX(SALARY)` returns the maximum salary field value.
`MIN`	The minimum value in any field expression. For example, `MIN(SALARY)` returns the minimum salary field value.

Examples

Copy

SELECT SUM (Sales_Data.Amount) AS agg FROM Sales_Data

SELECT AVG (Sales_Data.Amount) AS agg FROM Sales_Data

SELECT COUNT (Sales_Data.Amount) AS agg FROM Sales_Data

SELECT MAX (Sales_Data.Amount) AS agg FROM Sales_Data WHERE Sales_Data.Amount < 3000

SELECT MIN (Sales_Data.Amount) AS agg FROM Sales_Data WHERE Sales_Data.Amount > 3000

You cannot use an aggregate function as an argument to other functions. If you do, FileMaker software returns the error code 8309 ("Expressions involving aggregations are not supported"). For example, the following statement is not valid because the aggregate function SUM cannot be used as an argument to the function ROUND:

Example

Copy

SELECT ROUND(SUM(Salary), 0) FROM Payroll

However, aggregate functions can use functions that return numbers as arguments. The following statement is valid.

Example

Copy

SELECT SUM(ROUND(Salary, 0)) FROM Payroll