SQL Reference

Chapter 2 | Supported standards 29
SQL functions
FileMaker SQL supports many functions you can use in expressions. Some of the functions return
characters strings, some return numbers, some return dates, and some return values that depend
on conditions met by the function arguments.
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 DIS
TINCT operator to eliminate duplicate
values. For example:
COUNT (DISTINCT last_name)
In this example, only unique last name va
lues are counted.
Aggregate function Returns
SUM The total of the values in a numeric field expression. For exampl
e, SUM(SALARY) returns
the sum of all salary field values.
AVG The average of the valu
es 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 expr
ession. For example, MIN(SALARY) returns the
minimum salary field value.
Examples
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