An aggregate function returning a summary of data returned in a query
Format
COUNT(*) |
{ AVG | MAX | MIN | SUM | COUNT } ( [ ALL | DISTINCT ] expression
)
Remarks
Aggregate functions operate over the whole rowset. There are some rules regarding usage of aggregate functions:
- For SUM and AVG functions, the argument must be numeric.
- The optional keyword DISTINCT can preceed the expression argument to a scalar function. When used duplicate values are removed before the aggregate function is applied. The alternative to DISTINCT is ALL. ALL is assumed if neither DISTINCT or ALL is specified. DISTINCT can be used with any aggregate function, but will have no effect on the result when used with MIN or MAX.
- The expression argument to an aggregate function cannot contain any other aggregate functions or scalar subqueries.
- Aggregate functions can only be used in the column-list, or HAVING clause of query-expr.
- If one column has an aggregate function, all other columns in the query must also have an aggregate column, except when the column is referred to by a GROUP BY clause.
Example:
Example 1: Get the total number of points:
SELECT COUNT(*) AS PointCount FROM CDBPOINT
Example 2: Get the average flow from all wells:
SELECT AVG(CurrentValue) AS AvgFlow FROM CPointAlg WHERE FullName LIKE '%Flow'