Navigation:  Advantage SQL > Supported SQL Grammar >

Supported Aggregate (column) Functions

Advantage SQL Engine

Previous pageReturn to chapter overviewNext page

expr = column name or SQL expression

AVG( expr )

Calculates the average of a set of values.

COUNT( [ ALL | DISTINCT ] expr )

Returns the number of non-NULL column items that satisfy a query’s search condition. The ALL keyword is the default action and the DISTINCT keyword returns the number of non-NULL and distinct column items.

COUNT(*)

Returns the number of rows that satisfy a query’s search condition.

MAX( expr )

Returns the maximum of a set of values.

MIN( expr )

Returns the minimum of a set of values.

SUM( expr )

Totals the values in a set of numeric values.

GROUP_CONCAT( [DISTINCT] expr [ORDER BY expr [DESC], ...] [SEPARATOR string_literal] [MAX_LENGTH integer] )

Returns the non-NULL character values in the group concatenated into a single string value. The values in the string are separated using comma (,) if SEPARATOR is not specified. The maximum length of result is 1024 character if the MAX_LENGTH is not specified. The MAX_LENGTH can be up to 3200 characters. If the result is longer than the maximum length, the data truncation error will be returned. The ORDER BY clause can be used to sort the values in the result. The DISTINCT clause is used to remove duplicated values in the result.

 

Note NULL values are ignored; The ALL keyword is only supported with the COUNT aggregate. The DISTINCT keyword is supported with the COUNT and GROUP_CONCAT aggregates.

Examples

SELECT AVG(quota), AVG(sales) FROM salesinfo

SELECT AVG(100 * (sales/quota)) FROM salesinfo

 

SELECT COUNT(empid) FROM employees

SELECT COUNT(ordernum) FROM orders WHERE amount > 1000

SELECT COUNT(*) FROM orders WHERE amount > 1000

 

SELECT MIN(quota), MAX(quota) FROM salesinfo

SELECT MAX(100 * (sales/quota)) FROM salesinfo

SELECT MIN(100 * (sales/quota)) FROM salesinfo

 

SELECT SUM(quota), SUM(sales) FROM salesinfo

SELECT SUM(sales - quota) FROM salesinfo

 

SELECT Branch, GROUP_CONCAT( lastname ) FROM employees GROUP BY Branch

 

SELECT

  Branch,

  GROUP_CONCAT( lastname + ',' + Firstname SEPARATOR ';' )

FROM employees

GROUP BY Branch

 

SELECT

  GROUP_CONCAT( DISTINCT language ORDER BY language )

FROM employees