SQL Reference
Table Of Contents
- Chapter 1 Introduction
- Chapter 2 Supported standards
- Support for Unicode characters
- SQL statements
- SELECT statement
- SQL clauses
- FROM clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- UNION operator
- ORDER BY clause
- OFFSET and FETCH FIRST clauses
- FOR UPDATE clause
- DELETE statement
- INSERT statement
- UPDATE statement
- CREATE TABLE statement
- TRUNCATE TABLE statement
- ALTER TABLE statement
- CREATE INDEX statement
- DROP INDEX statement
- SQL expressions
- SQL functions
- FileMaker system objects
- Reserved SQL keywords
- Index
Chapter 2 | Supported standards 29
Example
COUNT (DISTINCT last_name)
In this example, only unique last name values are counted.
Example
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 funct
ions. If you do, FileMaker
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
SELECT ROUND(SUM(Salary), 0) FROM Payroll
However, aggregate functions can use functions that return numbers as arguments. The following
statement is valid.
Example
SELECT SUM(ROUND(Salary, 0)) FROM Payroll
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.