SQL Reference

Chapter 2 | Supported standards 28
The following example shows the importance of precedence:
WHERE salary > 40000 OR hire_date > (DATE '2008-01-30') AND dept =
'D101'
Because AND is evaluated first, this query retrieves employees in department D101 hired after
January 30, 2008, as well as every employee making more than $40,000, no matter what
department or hire date.
To force the clause to be evaluated in a different order, use parentheses to enclose the conditions
to be evaluated first. For example:
WHERE (salary > 40000 OR hire_date > DATE '2008-01-30') AND dept =
'D101'
retrieves employees in department D101 that either make more than $40,000 or were hired after
January 30, 2008.
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 DISTINCT operator to eliminate duplicate
values. For example:
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.