GridGain Developers Hub
GitHub logo GridGain iso GridGain.com
GridGain Software Documentation

Aggregate Functions

AVG

AVG ([DISTINCT] expression)

Parameters

  • DISTINCT - optional keyword. If presents, will average the unique values.

Description

The average (mean) value. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.

Examples

Calculating average players' age:

SELECT AVG(age) "AverageAge" FROM Players;

BIT_AND

BIT_AND (expression)

Description

The bitwise AND of all non-null values. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.

A logical AND operation is performed on each pair of corresponding bits of two binary expressions of equal length.

In each pair, it returns 1 if the first bit is 1 AND the second bit is 1. Else, it returns 0.

BIT_OR

BIT_OR (expression)

Description

The bitwise OR of all non-null values. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.

A logical OR operation is performed on each pair of corresponding bits of two binary expressions of equal length.

In each pair, the result is 1 if the first bit is 1 OR the second bit is 1 OR both bits are 1, and otherwise the result is 0.

BOOL_AND

BOOL_AND (boolean)

Description

Returns true if all expressions are true. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements.

Example

SELECT item, BOOL_AND(price > 10) FROM Items GROUP BY item;

BOOL_OR

BOOL_AND  (boolean)

Description

Returns true if any expression is true. If no entries​ are selected, the result is NULL. Aggregates are only allowed in select statements.

Example

SELECT BOOL_OR(CITY LIKE 'W%') FROM Users;

COUNT

COUNT (* | [DISTINCT] expression)

Description

The count of all entries or of the non-null values. This method returns a long. If no entries are selected, the result is 0. Aggregates are only allowed in select statements.

Example

Calculate the number of players in every city:

SELECT city_id, COUNT(*) FROM Players GROUP BY city_id;

GROUP_CONCAT

GROUP_CONCAT([DISTINCT] expression || [expression || [expression ...]]
  [ORDER BY expression [ASC|DESC], [[ORDER BY expression [ASC|DESC]]]
  [SEPARATOR expression])

The expression can be a concatenation of columns and strings using the || operator, for example: column1 || "=" || column2.

Parameters

  • DISTINCT - filters the result set for unique sets of expressions.

  • expression - specifies an expression that may be a column name, a result of another function, or a math operation.

  • ORDER BY - orders rows by expression.

  • SEPARATOR - overrides a string separator. By default, the separator character is the comma ','.

Description

Concatenates strings with a separator. The default separator is a ',' (without whitespace). This method returns a string. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements.

Example

Group all players' names in one row:

SELECT GROUP_CONCAT(name ORDER BY id SEPARATOR ', ') FROM Players;

MAX

MAX (expression)

Parameters

  • expression - may be a column name, a result of another function, or a math operation.

Description

Returns the highest value. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.

Example

Return the height of the ​tallest player:

SELECT MAX(height) FROM Players;

MIN

MIN (expression)

Parameters

  • expression - may be a column name, the result of another function, or a math operation.

Description

Returns the lowest value. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.

Example

Return the age of the youngest player:

SELECT MIN(age) FROM Players;

SUM

SUM ([DISTINCT] expression)

Parameters

  • DISTINCT - accumulate unique values only.

  • expression - may be a column name, the result of another function, or a math operation.

Description

Returns the sum of all values. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements. The data type of the returned value depends on the parameter data.

Example

Get the total number of goals scored by all players:

SELECT SUM(goal) FROM Players;

SELECTIVITY

SELECTIVITY (expression)

Parameters

  • expression - may be a column name.

Description

Estimates the selectivity (0-100) of a value. The value is defined as (100 * distinctCount / rowCount). The selectivity of 0 rows is 0 (unknown). Aggregates are only allowed in select statements.

Example

Calculate the selectivity of the first_name and second_name columns:

SELECT SELECTIVITY(first_name), SELECTIVITY(second_name) FROM Player
  WHERE ROWNUM() < 20000;

STDDEV_POP

STDDEV_POP ([DISTINCT] expression)

Parameters

  • DISTINCT - calculate unique value only.

  • expression - may be a column name.

Description

Returns the population standard deviation. This method returns a double. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements.

Example

Calculate the standard deviation for Players' age:

SELECT STDDEV_POP(age) from Players;

STDDEV_SAMP

STDDEV_SAMP ([DISTINCT] expression)

Parameters

  • DISTINCT - calculate unique values only.

  • expression - may be a column name.

Description

Calculates the sample standard deviation. This method returns a double. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements.

Example

Calculates the sample standard deviation for Players' age:

SELECT STDDEV_SAMP(age) from Players;

VAR_POP

VAR_POP ([DISTINCT] expression)

Parameters

  • DISTINCT - calculate unique values only.

  • expression - may be a column name.

Description

Calculates the population variance (square of the population standard deviation). This method returns a double. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements.

Example

Calculate the variance of Players' age:

SELECT VAR_POP (age) from Players;

VAR_SAMP

VAR_SAMP ([DISTINCT] expression)

Parameters

  • DISTINCT - calculate unique values only.

  • expression - may be a column name.

Description

Calculates the sample variance (square of the sample standard deviation). This method returns a double. If no entries are selected, the result is NULL. Aggregates are only allowed in select statements.

Example

Calculate the variance of Players' age:

SELECT VAR_SAMP(age) FROM Players;