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

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

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