SELECT (aggregate)

An aggregate SELECT command performs a calculation on a number of rows and returns a single value.

Syntax

SELECT aggr_expression [ AS alias_name ],...
 FROM [database_name.]table_name
  [ WHERE condition ];

The following aggregate functions (depicted by aggr_expression in the syntax) are supported.

Table 1. Aggregate functions
Function	Result returned
`max(scalar_column_expr)`	This returns the maximum numeric value for the column expression from the rows that satisfy the SELECT condition.
`min(scalar_column_expr)`	This returns the minimum numeric value for the column expression from the rows that satisfy the SELECT condition.
`avg(scalar_column_expr)`	This returns the average numeric value for the column expression from the rows that satisfy the SELECT condition.
`sum(scalar_column_expr)`	This returns the sum (total) of the numeric values for the column expression from the rows that meet the SELECT condition.
`count(scalar_column_expr)` `count(*)`	This returns the total number of rows that satisfy the SELECT condition.
`dist(scalar_column_expr, value)`	This returns the total number of rows for which the column equals the specified value. The result of: `dist(scalar_column_expr, value)` is equivalent to: `SELECT count(scalar_column_expr) FROM table_name WHERE scalar_column_expr = value;`

Following an aggregate expression, you can include the AS keyword followed by an alias. This alias is a replacement heading for the aggregate expression, and is displayed in the query results.

The maximum length of a column name or alias is 40 characters.

If you include a WHERE clause, only rows satisfying the criteria specified in the condition are returned.

Examples

The following example returns the highest Severity value, the average Severity value, and the number of rows for which the Severity is equal to 4:

select MAX(Severity), AVG(Severity), DIST(Severity, 4) from alerts.status;

The following example returns the number of rows for which the value of Node is myhost:

select DIST(Node, 'myhost') from alerts.status;

The following examples perform comparisons by using the getdate function, which returns the current time:

select MAX(getdate-LastOccurrence) from alerts.status;

select AVG((getdate-LastOccurrence)/60) as ResponseTime from alerts.status 
where OwnerUID=34;