Aggregation Functions

This page lists the aggregation functions available in the GSQL query language. These functions take a single SET/BAG, SetAccum/BagAccum, or ListAccum as the parameter and return an aggregated value from all members of the set or bag.

The DISTINCT keyword can be used to include repeated values only once when aggregating.

avg()

Syntax

avg( [DISTINCT] setExp )

Description

Returns the average of all elements in a set or bag. The function can only take set/bag expressions whose members are numeric types.

Return type

A numeric type. If all members of the set/bag expression are integers, the return value will also be rounded down to be an integer.

Parameters

Parameter Description Data type

setExp

An expression that evaluates to a SET, BAG, SetAccum or BagAccum

SET, BAG, SetAccum, BagAccum

Example

avg([5, 4, 1, 0, 0, 0]) -> 1
avg([3, 2, 1]) -> 2

count()

Syntax

count( [DISTINCT] setExp )

Description

Returns the size of the set or bag.

Return type

INT

Parameters

Parameter Description Data type

setExp

An expression that evaluates to a SET, BAG, SetAccum or BagAccum

SET, BAG, SetAccum, BagAccum

Example

count([1, 2, 3]) => 3
count([1, 1, 2, 2) => 4

max()

Syntax

max( [DISTINCT] setExp )

Description

Returns the member with the maximum value in a set or bag. The function can only take set/bag expressions whose members are numeric types.

Return type

A numeric type.

Parameters

Parameter Description Data type

setExp

An expression that evaluates to a SET, BAG, SetAccum or BagAccum

SET, BAG, SetAccum, BagAccum

Example

max([1, 2, -3, 4]) => 4
max([1, 1, 3, 3]) => 3

min()

Syntax

min( [DISTINCT] setExp )

Description

Returns the member with the minimum value in a set or bag. The function can only take set/bag expressions whose members are numeric types.

Return type

A numberic type.

Parameters

Parameter Description Data type

setExp

An expression that evaluates to a SET, BAG, SetAccum or BagAccum

SET, BAG, SetAccum, BagAccum

Example

min([1, 2, -3, 4]) => -3
min([1, 1, 3, 3]) => 1

sum()

Syntax

sum( [DISTINCT] setExp )

Description

Returns the sum of all members in a set or bag. The function can only take set/bag expressions whose members are numeric types.

Return type

A numeric type.

Parameters

Parameter Description Data type

setExp

An expression that evaluates to a SET, BAG, SetAccum or BagAccum

SET, BAG, SetAccum, BagAccum

Example

sum([1, 2, -3, 4]) => 4
sum([1, 1, 3, 3]) => 8