Aggregate Functions
The adapter supports the following SQL-92 summary functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a powerful reporting tool.
Examples of Aggregate Functions
COUNT
Returns the number of rows matching the query criteria.
SELECT COUNT(*) FROM Customers WHERE Country = US
COUNT_DISTINCT
Returns the number of distinct, non-null field values matching the query criteria.
SELECT COUNT_DISTINCT(City) AS DistinctValues FROM Customers
COUNT(DISTINCT)
Returns the number of distinct, non-null field values matching the query criteria.
SELECT COUNT(DISTINCT City) AS DistinctValues FROM Customers
AVG
Returns the average of the column values.
SELECT CompanyName, AVG(Balance) FROM Customers GROUP BY CompanyName
MIN
Returns the minimum column value.
SELECT MIN(Balance), CompanyName FROM Customers GROUP BY CompanyName
MAX
Returns the maximum column value.
SELECT CompanyName, MAX(Balance) FROM Customers GROUP BY CompanyName
SUM
Returns the total sum of the column values.
SELECT SUM(Balance) FROM Customers WHERE Country = US