Aggregate functions in Google Standard SQL

Stay organized with collections Save and categorize content based on your preferences.

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

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 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 arguments in this function and how to use them, 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     |
+-----------+

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 arguments in this function and how to use them, 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 arguments in this function and how to use them, 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 input values.

To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.

Caveats:

  • 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.
  • If the input is empty, does not come from the FROM clause, or contains only NULLs, returns NULL.
  • If the input contains NaN, returns NaN.
  • If the input contains [+|-]Infinity and the input is a singleton, returns [+|-]Infinity. If the input is not a singleton, returns NaN.
  • If there is numeric overflow, produces an error.

Supported Argument Types

Any numeric input type.

Returned Data Types

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

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 arguments in this function and how to use them, 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 arguments in this function and how to use them, 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 arguments in this function and how to use them, 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

1.

COUNT(*)

2.

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

Description

  1. Returns the number of rows in the input.
  2. Returns the number of rows with expression evaluated to any value other than NULL.

To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.

Supported Argument Types

expression can be any data type. If DISTINCT is present, expression can only be a data type that is groupable.

Return Data Types

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       |
+------------+---------+

If you want to count the number of distinct values of an expression for which a certain condition is satisfied, this is one recipe that you can use:

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

Here, IF will return the value of expression if condition is TRUE, or NULL otherwise. The surrounding COUNT(DISTINCT ...) will ignore the NULL values, so it will count only the distinct values of expression for which condition is TRUE.

For example, to count 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                 |
+-------------------+

Or to count 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                            |
+------------------------------+

COUNTIF

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

Description

Returns the count of TRUE values for expression. Returns 0 if there are zero input rows, or if expression evaluates to FALSE or NULL for all rows.

Since expression must be a BOOL, the form COUNTIF(DISTINCT ...) is generally not useful: there is only one distinct value of TRUE. So COUNTIF(DISTINCT ...) will return 1 if expression evaluates to TRUE for one or more input rows, or 0 otherwise. Usually when someone wants to combine COUNTIF and DISTINCT, they want to count the number of distinct values of an expression for which a certain condition is satisfied. One recipe to achieve this is the following:

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

Note that this uses COUNT, not COUNTIF; the IF part has been moved inside. To learn more, see the examples for COUNT.

To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.

Supported Argument Types

BOOL

Return Data Types

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 arguments in this function and how to use them, 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 arguments in this function and how to use them, 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 value of non-NULL expressions. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows. Returns NaN if the input contains a NaN.

To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.

Supported Argument Types

Any orderable data type except for ARRAY.

Return Data Types

Same as the data type used as the input values.

Examples

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

+-----+
| max |
+-----+
| 55  |
+-----+

MIN

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

Description

Returns the minimum value of non-NULL expressions. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows. Returns NaN if the input contains a NaN.

To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.

Supported Argument Types

Any orderable data type except for ARRAY.

Return Data Types

Same as the data type used as 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 arguments in this function and how to use them, 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.

If the expression is a floating point value, the sum is non-deterministic, which means you might receive a different result each time you use this function.

To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.

Supported Argument Types

Any supported numeric data types.

Return Data Types

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

Special cases:

Returns NULL if the input contains only NULLs.

Returns NULL if the input contains no rows.

Returns Inf if the input contains Inf.

Returns -Inf if the input contains -Inf.

Returns NaN if the input contains a NaN.

Returns NaN if the input contains a combination of Inf and -Inf.

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 |
+------+