Aggregate functions in GoogleSQL

GoogleSQL for Spanner supports the following general aggregate functions. To learn about the syntax for aggregate function calls, see Aggregate function calls.

Function list

Name	Summary
`ANY_VALUE`	Gets an expression for some row.
`ARRAY_AGG`	Gets an array of values.
`ARRAY_CONCAT_AGG`	Concatenates arrays and returns a single array as a result.
`AVG`	Gets the average of non-`NULL` values.
`BIT_AND`	Performs a bitwise AND operation on an expression.
`BIT_OR`	Performs a bitwise OR operation on an expression.
`BIT_XOR`	Performs a bitwise XOR operation on an expression.
`COUNT`	Gets the number of rows in the input, or the number of rows with an expression evaluated to any value other than `NULL`.
`COUNTIF`	Gets the number of `TRUE` values for an expression.
`LOGICAL_AND`	Gets the logical AND of all non-`NULL` expressions.
`LOGICAL_OR`	Gets the logical OR of all non-`NULL` expressions.
`MAX`	Gets the maximum non-`NULL` value.
`MIN`	Gets the minimum non-`NULL` value.
`STDDEV`	An alias of the `STDDEV_SAMP` function. For more information, see Statistical aggregate functions.
`STDDEV_SAMP`	Computes the sample (unbiased) standard deviation of the values. For more information, see Statistical aggregate functions.
`STRING_AGG`	Concatenates non-`NULL` `STRING` or `BYTES` values.
`SUM`	Gets the sum of non-`NULL` values.
`VAR_SAMP`	Computes the sample (unbiased) variance of the values. For more information, see Statistical aggregate functions.
`VARIANCE`	An alias of `VAR_SAMP`. For more information, see Statistical aggregate functions.

`ANY_VALUE`

ANY_VALUE(
  expression
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Returns expression for some row chosen from the group. Which row is chosen is nondeterministic, not random. Returns NULL when the input produces no rows. Returns NULL when expression or expression2 is NULL for all rows in the group.

ANY_VALUE behaves as if IGNORE NULLS is specified; rows for which expression is NULL are not considered and won't be selected.

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

Supported Argument Types

Any

Returned Data Types

Matches the input data type.

Examples

SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

/*-----------*
 | any_value |
 +-----------+
 | apple     |
 *-----------*/

WITH
  Store AS (
    SELECT 20 AS sold, "apples" AS fruit
    UNION ALL
    SELECT 30 AS sold, "pears" AS fruit
    UNION ALL
    SELECT 30 AS sold, "bananas" AS fruit
    UNION ALL
    SELECT 10 AS sold, "oranges" AS fruit
  )
SELECT ANY_VALUE(fruit HAVING MAX sold) AS a_highest_selling_fruit FROM Store;

/*-------------------------*
 | a_highest_selling_fruit |
 +-------------------------+
 | pears                   |
 *-------------------------*/

WITH
  Store AS (
    SELECT 20 AS sold, "apples" AS fruit
    UNION ALL
    SELECT 30 AS sold, "pears" AS fruit
    UNION ALL
    SELECT 30 AS sold, "bananas" AS fruit
    UNION ALL
    SELECT 10 AS sold, "oranges" AS fruit
  )
SELECT ANY_VALUE(fruit HAVING MIN sold) AS a_lowest_selling_fruit FROM Store;

/*-------------------------*
 | a_lowest_selling_fruit  |
 +-------------------------+
 | oranges                 |
 *-------------------------*/

`ARRAY_AGG`

ARRAY_AGG(
  [ DISTINCT ]
  expression
  [ { IGNORE | RESPECT } NULLS ]
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Returns an ARRAY of expression values.

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

Supported Argument Types

All data types except ARRAY.

Returned Data Types

ARRAY

If there are zero input rows, this function returns NULL.

Examples

SELECT ARRAY_AGG(x) AS array_agg FROM UNNEST([2, 1,-2, 3, -2, 1, 2]) AS x;

/*-------------------------*
 | array_agg               |
 +-------------------------+
 | [2, 1, -2, 3, -2, 1, 2] |
 *-------------------------*/

SELECT ARRAY_AGG(DISTINCT x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

/*---------------*
 | array_agg     |
 +---------------+
 | [2, 1, -2, 3] |
 *---------------*/

SELECT ARRAY_AGG(x IGNORE NULLS) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

/*-------------------*
 | array_agg         |
 +-------------------+
 | [1, -2, 3, -2, 1] |
 *-------------------*/

WITH vals AS
  (
    SELECT 1 x, 'a' y UNION ALL
    SELECT 1 x, 'b' y UNION ALL
    SELECT 2 x, 'a' y UNION ALL
    SELECT 2 x, 'c' y
  )
SELECT x, ARRAY_AGG(y) as array_agg
FROM vals
GROUP BY x;

/*---------------*
 | x | array_agg |
 +---------------+
 | 1 | [a, b]    |
 | 2 | [a, c]    |
 *---------------*/

`ARRAY_CONCAT_AGG`

ARRAY_CONCAT_AGG(
  expression
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Concatenates elements from expression of type ARRAY, returning a single array as a result.

This function ignores NULL input arrays, but respects the NULL elements in non-NULL input arrays. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows.

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

Supported Argument Types

ARRAY

Returned Data Types

ARRAY

Examples

SELECT ARRAY_CONCAT_AGG(x) AS array_concat_agg FROM (
  SELECT [NULL, 1, 2, 3, 4] AS x
  UNION ALL SELECT NULL
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

/*-----------------------------------*
 | array_concat_agg                  |
 +-----------------------------------+
 | [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
 *-----------------------------------*/

`AVG`

AVG(
  [ DISTINCT ]
  expression
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Returns the average of non-NULL values in an aggregated group.

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

Caveats:

If the aggregated group is empty or the argument is NULL for all rows in the group, returns NULL.
If the argument is NaN for any row in the group, returns NaN.
If the argument is [+|-]Infinity for any row in the group, returns either [+|-]Infinity or NaN.
If there is numeric overflow, produces an error.
If a floating-point type is returned, the result is non-deterministic, which means you might receive a different result each time you use this function.

Supported Argument Types

Any numeric input type

Returned Data Types

INPUT	`INT64`	`NUMERIC`	`FLOAT32`	`FLOAT64`
OUTPUT	`FLOAT64`	`NUMERIC`	`FLOAT64`	`FLOAT64`

Examples

SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;

/*-----*
 | avg |
 +-----+
 | 3   |
 *-----*/

SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;

/*------*
 | avg  |
 +------+
 | 2.75 |
 *------*/

`BIT_AND`

BIT_AND(
  [ DISTINCT ]
  expression
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Performs a bitwise AND operation on expression and returns the result.

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

Supported Argument Types

INT64

Returned Data Types

INT64

Examples

SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;

/*---------*
 | bit_and |
 +---------+
 | 1       |
 *---------*/

`BIT_OR`

BIT_OR(
  [ DISTINCT ]
  expression
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Performs a bitwise OR operation on expression and returns the result.

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

Supported Argument Types

INT64

Returned Data Types

INT64

Examples

SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;

/*--------*
 | bit_or |
 +--------+
 | 61601  |
 *--------*/

`BIT_XOR`

BIT_XOR(
  [ DISTINCT ]
  expression
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Performs a bitwise XOR operation on expression and returns the result.

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

Supported Argument Types

INT64

Returned Data Types

INT64

Examples

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;

/*---------*
 | bit_xor |
 +---------+
 | 4860    |
 *---------*/

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

/*---------*
 | bit_xor |
 +---------+
 | 5678    |
 *---------*/

SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

/*---------*
 | bit_xor |
 +---------+
 | 4860    |
 *---------*/

`COUNT`

COUNT(*)

COUNT(
  [ DISTINCT ]
  expression
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Gets the number of rows in the input or the number of rows with an expression evaluated to any value other than NULL.

Definitions

*: Use this value to get the number of all rows in the input.
expression: A value of any data type that represents the expression to evaluate. If DISTINCT is present, expression can only be a data type that is groupable.
DISTINCT: To learn more, see Aggregate function calls.
HAVING { MAX | MIN }: To learn more, see Aggregate function calls.

Details

To count the number of distinct values of an expression for which a certain condition is satisfied, you can use the following recipe:

COUNT(DISTINCT IF(condition, expression, NULL))

IF returns the value of expression if condition is TRUE, or NULL otherwise. The surrounding COUNT(DISTINCT ...) ignores the NULL values, so it counts only the distinct values of expression for which condition is TRUE.

To count the number of non-distinct values of an expression for which a certain condition is satisfied, consider using the COUNTIF function.

Return type

INT64

Examples

You can use the COUNT function to return the number of rows in a table or the number of distinct values of an expression. For example:

SELECT
  COUNT(*) AS count_star,
  COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

/*------------+--------------*
 | count_star | count_dist_x |
 +------------+--------------+
 | 4          | 3            |
 *------------+--------------*/

SELECT COUNT(*) AS count_star, COUNT(x) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

/*------------+---------*
 | count_star | count_x |
 +------------+---------+
 | 5          | 4       |
 *------------+---------*/

The following query counts the number of distinct positive values of x:

SELECT COUNT(DISTINCT IF(x > 0, x, NULL)) AS distinct_positive
FROM UNNEST([1, -2, 4, 1, -5, 4, 1, 3, -6, 1]) AS x;

/*-------------------*
 | distinct_positive |
 +-------------------+
 | 3                 |
 *-------------------*/

The following query counts the number of distinct dates on which a certain kind of event occurred:

WITH Events AS (
  SELECT DATE '2021-01-01' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-02' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-02' AS event_date, 'FAILURE' AS event_type
  UNION ALL
  SELECT DATE '2021-01-03' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
  UNION ALL
  SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
)
SELECT
  COUNT(DISTINCT IF(event_type = 'FAILURE', event_date, NULL))
    AS distinct_dates_with_failures
FROM Events;

/*------------------------------*
 | distinct_dates_with_failures |
 +------------------------------+
 | 2                            |
 *------------------------------*/

The following query counts the number of distinct ids that exist in both the customers and vendor tables:

WITH
  customers AS (
    SELECT 1934 AS id, 'a' AS team UNION ALL
    SELECT 2991, 'b' UNION ALL
    SELECT 3988, 'c'),
  vendors AS (
    SELECT 1934 AS id, 'd' AS team UNION ALL
    SELECT 2991, 'e' UNION ALL
    SELECT 4366, 'f')
SELECT
  COUNT(DISTINCT IF(id IN (SELECT id FROM customers), id, NULL)) AS result
FROM vendors;

/*--------*
 | result |
 +--------+
 | 2      |
 *--------*/

`COUNTIF`

COUNTIF(
  [ DISTINCT ]
  expression
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Gets the number of TRUE values for an expression.

Definitions

expression: A BOOL value that represents the expression to evaluate.
DISTINCT: To learn more, see Aggregate function calls.
HAVING { MAX | MIN }: To learn more, see Aggregate function calls.

Details

The function signature COUNTIF(DISTINCT ...) is generally not useful. If you would like to use DISTINCT, use COUNT with DISTINCT IF. For more information, see the COUNT function.

Return type

INT64

Examples

SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;

/*--------------+--------------*
 | num_negative | num_positive |
 +--------------+--------------+
 | 3            | 4            |
 *--------------+--------------*/

`LOGICAL_AND`

LOGICAL_AND(
  expression
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Returns the logical AND of all non-NULL expressions. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows.

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

Supported Argument Types

BOOL

Return Data Types

BOOL

Examples

LOGICAL_AND returns FALSE because not all of the values in the array are less than 3.

SELECT LOGICAL_AND(x < 3) AS logical_and FROM UNNEST([1, 2, 4]) AS x;

/*-------------*
 | logical_and |
 +-------------+
 | FALSE       |
 *-------------*/

`LOGICAL_OR`

LOGICAL_OR(
  expression
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Returns the logical OR of all non-NULL expressions. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows.

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

Supported Argument Types

BOOL

Return Data Types

BOOL

Examples

LOGICAL_OR returns TRUE because at least one of the values in the array is less than 3.

SELECT LOGICAL_OR(x < 3) AS logical_or FROM UNNEST([1, 2, 4]) AS x;

/*------------*
 | logical_or |
 +------------+
 | TRUE       |
 *------------*/

`MAX`

MAX(
  expression
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Returns the maximum non-NULL value in an aggregated group.

Caveats:

If the aggregated group is empty or the argument is NULL for all rows in the group, returns NULL.
If the argument is NaN for any row in the group, returns NaN.

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

Supported Argument Types

Any orderable data type except for ARRAY.

Return Data Types

The data type of the input values.

Examples

SELECT MAX(x) AS max
FROM UNNEST([8, 37, 55, 4]) AS x;

/*-----*
 | max |
 +-----+
 | 55  |
 *-----*/

`MIN`

MIN(
  expression
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Returns the minimum non-NULL value in an aggregated group.

Caveats:

If the aggregated group is empty or the argument is NULL for all rows in the group, returns NULL.
If the argument is NaN for any row in the group, returns NaN.

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

Supported Argument Types

Any orderable data type except for ARRAY.

Return Data Types

The data type of the input values.

Examples

SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;

/*-----*
 | min |
 +-----+
 | 4   |
 *-----*/

`STRING_AGG`

STRING_AGG(
  [ DISTINCT ]
  expression [, delimiter]
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Returns a value (either STRING or BYTES) obtained by concatenating non-NULL values. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows.

If a delimiter is specified, concatenated values are separated by that delimiter; otherwise, a comma is used as a delimiter.

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

Supported Argument Types

Either STRING or BYTES.

Return Data Types

Either STRING or BYTES.

Examples

SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

/*------------------------*
 | string_agg             |
 +------------------------+
 | apple,pear,banana,pear |
 *------------------------*/

SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

/*------------------------------*
 | string_agg                   |
 +------------------------------+
 | apple & pear & banana & pear |
 *------------------------------*/

SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

/*-----------------------*
 | string_agg            |
 +-----------------------+
 | apple & pear & banana |
 *-----------------------*/

`SUM`

SUM(
  [ DISTINCT ]
  expression
  [ HAVING { MAX | MIN } expression2 ]
)

Description

Returns the sum of non-NULL values in an aggregated group.

To learn more about the optional aggregate clauses that you can pass into this function, see Aggregate function calls.

Caveats:

If the aggregated group is empty or the argument is NULL for all rows in the group, returns NULL.
If the argument is NaN for any row in the group, returns NaN.
If the argument is [+|-]Infinity for any row in the group, returns either [+|-]Infinity or NaN.
If there is numeric overflow, produces an error.
If a floating-point type is returned, the result is non-deterministic, which means you might receive a different result each time you use this function.

Supported Argument Types

Any supported numeric data type

Return Data Types

INPUT	`INT64`	`NUMERIC`	`FLOAT32`	`FLOAT64`
OUTPUT	`INT64`	`NUMERIC`	`FLOAT64`	`FLOAT64`

Examples

SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

/*-----*
 | sum |
 +-----+
 | 25  |
 *-----*/

SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

/*-----*
 | sum |
 +-----+
 | 15  |
 *-----*/

SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;

/*------*
 | sum  |
 +------+
 | NULL |
 *------*/