Aggregate functions

GoogleSQL for BigQuery 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.
APPROX_COUNT_DISTINCT Gets the approximate result for COUNT(DISTINCT expression).
For more information, see Approximate aggregate functions.
APPROX_QUANTILES Gets the approximate quantile boundaries.
For more information, see Approximate aggregate functions.
APPROX_TOP_COUNT Gets the approximate top elements and their approximate count.
For more information, see Approximate aggregate functions.
APPROX_TOP_SUM Gets the approximate top elements and sum, based on the approximate sum of an assigned weight.
For more information, see Approximate aggregate functions.
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.
AVG (Differential Privacy) DIFFERENTIAL_PRIVACY-supported AVG.

Gets the differentially-private average of non-NULL, non-NaN values in a query with a DIFFERENTIAL_PRIVACY clause.

For more information, see Differential privacy functions.
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.
CORR Computes the Pearson coefficient of correlation of a set of number pairs.
For more information, see Statistical aggregate functions.
COUNT Gets the number of rows in the input, or the number of rows with an expression evaluated to any value other than NULL.
COUNT (Differential Privacy) DIFFERENTIAL_PRIVACY-supported COUNT.

Signature 1: Gets the differentially-private count of rows in a query with a DIFFERENTIAL_PRIVACY clause.

Signature 2: Gets the differentially-private count of rows with a non-NULL expression in a query with a DIFFERENTIAL_PRIVACY clause.

For more information, see Differential privacy functions.
COUNTIF Gets the count of TRUE values for an expression.
COVAR_POP Computes the population covariance of a set of number pairs.
For more information, see Statistical aggregate functions.
COVAR_SAMP Computes the sample covariance of a set of number pairs.
For more information, see Statistical aggregate functions.
GROUPING Checks if a groupable value in the GROUP BY clause is aggregated.
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.
MAX_BY Synonym for ANY_VALUE(x HAVING MAX y).
MIN Gets the minimum non-NULL value.
MIN_BY Synonym for ANY_VALUE(x HAVING MIN y).
PERCENTILE_CONT (Differential Privacy) DIFFERENTIAL_PRIVACY-supported PERCENTILE_CONT.

Computes a differentially-private percentile across privacy unit columns in a query with a DIFFERENTIAL_PRIVACY clause.

For more information, see Differential privacy functions.
ST_CENTROID_AGG Gets the centroid of a set of GEOGRAPHY values.
For more information, see Geography functions.
ST_EXTENT Gets the bounding box for a group of GEOGRAPHY values.
For more information, see Geography functions.
ST_UNION_AGG Aggregates over GEOGRAPHY values and gets their point set union.
For more information, see Geography functions.
STDDEV An alias of the STDDEV_SAMP function.
For more information, see Statistical aggregate functions.
STDDEV_POP Computes the population (biased) standard deviation of the values.
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.
SUM (Differential Privacy) DIFFERENTIAL_PRIVACY-supported SUM.

Gets the differentially-private sum of non-NULL, non-NaN values in a query with a DIFFERENTIAL_PRIVACY clause.

For more information, see Differential privacy functions.
VAR_POP Computes the population (biased) variance of the values.
For more information, see Statistical aggregate functions.
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 ]
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

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.

If the HAVING clause is included in the ANY_VALUE function, the OVER clause can't be used with this function.

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

To learn more about the OVER clause and how to use it, see Window 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     |
 *-----------*/

SELECT
  fruit,
  ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

/*--------+-----------*
 | fruit  | any_value |
 +--------+-----------+
 | pear   | pear      |
 | apple  | pear      |
 | banana | 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 ]
  [ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
  [ LIMIT n ]
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

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.

If this function is used with the OVER clause, it's part of a window function call. In a window function call, aggregate function clauses can't be used. To learn more about the OVER clause and how to use it, see Window function calls.

An error is raised if an array in the final query result contains a NULL element.

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] |
 *-------------------*/

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

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

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

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

WITH vals AS
  (
    SELECT 1 x UNION ALL
    SELECT -2 x UNION ALL
    SELECT 3 x UNION ALL
    SELECT -2 x UNION ALL
    SELECT 1 x
  )
SELECT ARRAY_AGG(DISTINCT x ORDER BY x) as array_agg
FROM vals;

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

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]    |
 *---------------*/

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

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

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(
  expression
  [ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
  [ LIMIT n ]
)

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. An error is raised, however, if an array in the final query result contains a NULL element. 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 FORMAT("%T", 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] |
 *-----------------------------------*/

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x))) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

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

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

/*--------------------------*
 | array_concat_agg         |
 +--------------------------+
 | [1, 2, 3, 4, 5, 6]       |
 *--------------------------*/

SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

/*------------------*
 | array_concat_agg |
 +------------------+
 | [5, 6, 7, 8, 9]  |
 *------------------*/

AVG

AVG(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

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.

This function can be used with the AGGREGATION_THRESHOLD clause.

If this function is used with the OVER clause, it's part of a window function call. In a window function call, aggregate function clauses can't be used. To learn more about the OVER clause and how to use it, see Window function calls.

AVG can be used with differential privacy. For more information, see Differentially private aggregate functions.

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
  • INTERVAL

Returned Data Types

INPUTINT64NUMERICBIGNUMERICFLOAT64INTERVAL
OUTPUTFLOAT64NUMERICBIGNUMERICFLOAT64INTERVAL

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 |
 *------*/

SELECT
  x,
  AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;

/*------+------*
 | x    | avg  |
 +------+------+
 | NULL | NULL |
 | 0    | 0    |
 | 2    | 1    |
 | 4    | 3    |
 | 4    | 4    |
 | 5    | 4.5  |
 *------+------*/

BIT_AND

BIT_AND(
  expression
)

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(
  expression
)

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
)

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

1.

COUNT(*)
[OVER over_clause]

2.

COUNT(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

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 aggregate clauses that you can pass into this function, see Aggregate function calls.

This function can be used with the AGGREGATION_THRESHOLD clause.

To learn more about the OVER clause and how to use it, see Window function calls.

This function with DISTINCT supports specifying collation.

COUNT can be used with differential privacy. For more information, see Differentially private aggregate functions.

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
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

/*------+------------+--------------*
 | x    | count_star | count_dist_x |
 +------+------------+--------------+
 | 1    | 3          | 2            |
 | 4    | 3          | 2            |
 | 4    | 3          | 2            |
 | 5    | 1          | 1            |
 *------+------------+--------------*/

SELECT
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(x) OVER (PARTITION BY MOD(x, 3)) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

/*------+------------+---------*
 | x    | count_star | count_x |
 +------+------------+---------+
 | NULL | 1          | 0       |
 | 1    | 3          | 3       |
 | 4    | 3          | 3       |
 | 4    | 3          | 3       |
 | 5    | 1          | 1       |
 *------+------------+---------*/

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
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

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 aggregate clauses that you can pass into this function, see Aggregate function calls.

This function can be used with the AGGREGATION_THRESHOLD clause.

To learn more about the OVER clause and how to use it, see Window 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            |
 *--------------+--------------*/

SELECT
  x,
  COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;

/*------+--------------*
 | x    | num_negative |
 +------+--------------+
 | NULL | 0            |
 | 0    | 1            |
 | -2   | 1            |
 | 3    | 1            |
 | 4    | 0            |
 | 5    | 0            |
 | 6    | 1            |
 | -7   | 2            |
 | -10  | 2            |
 *------+--------------*/

GROUPING

GROUPING(groupable_value)

Description

If a groupable item in the GROUP BY clause is aggregated (and thus not grouped), this function returns 1. Otherwise, this function returns 0.

Definitions:

  • groupable_value: An expression that represents a value that can be grouped in the GROUP BY clause.

Details:

The GROUPING function is helpful if you need to determine which rows are produced by which grouping sets. A grouping set is a group of columns by which rows can be grouped together. So, if you need to filter rows by a few specific grouping sets, you can use the GROUPING function to identify which grouping sets grouped which rows by creating a matrix of the results.

In addition, you can use the GROUPING function to determine the type of NULL produced by the GROUP BY clause. In some cases, the GROUP BY clause produces a NULL placeholder. This placeholder represents all groupable items that are aggregated (not grouped) in the current grouping set. This is different from a standard NULL, which can also be produced by a query.

For more information, see the following examples.

Returned Data Type

INT64

Examples

In the following example, it's difficult to determine which rows are grouped by the grouping value product_type or product_name. The GROUPING function makes this easier to determine.

Pay close attention to what's in the product_type_agg and product_name_agg column matrix. This determines how the rows are grouped.

product_type_agg product_name_agg Notes
1 0 Rows are grouped by product_name.
0 1 Rows are grouped by product_type.
0 0 Rows are grouped by product_type and product_name.
1 1 Grand total row. 
WITH
  Products AS (
    SELECT 'shirt' AS product_type, 't-shirt' AS product_name, 3 AS product_count UNION ALL
    SELECT 'shirt', 't-shirt', 8 UNION ALL
    SELECT 'shirt', 'polo', 25 UNION ALL
    SELECT 'pants', 'jeans', 6
  )
SELECT
  product_type,
  product_name,
  SUM(product_count) AS product_sum,
  GROUPING(product_type) AS product_type_agg,
  GROUPING(product_name) AS product_name_agg,
FROM Products
GROUP BY GROUPING SETS(product_type, product_name, ())
ORDER BY product_name;

/*--------------+--------------+-------------+------------------+------------------+
 | product_type | product_name | product_sum | product_type_agg | product_name_agg |
 +--------------+--------------+-------------+------------------+------------------+
 | NULL         | NULL         | 42          | 1                | 1                |
 | shirt        | NULL         | 36          | 0                | 1                |
 | pants        | NULL         | 6           | 0                | 1                |
 | NULL         | jeans        | 6           | 1                | 0                |
 | NULL         | polo         | 25          | 1                | 0                |
 | NULL         | t-shirt      | 11          | 1                | 0                |
 +--------------+--------------+-------------+------------------+------------------*/

In the following example, it's difficult to determine if NULL represents a NULL placeholder or a standard NULL value in the product_type column. The GROUPING function makes it easier to determine what type of NULL is being produced. If product_type_is_aggregated is 1, the NULL value for the product_type column is a NULL placeholder.

WITH
  Products AS (
    SELECT 'shirt' AS product_type, 't-shirt' AS product_name, 3 AS product_count UNION ALL
    SELECT 'shirt', 't-shirt', 8 UNION ALL
    SELECT NULL, 'polo', 25 UNION ALL
    SELECT 'pants', 'jeans', 6
  )
SELECT
  product_type,
  product_name,
  SUM(product_count) AS product_sum,
  GROUPING(product_type) AS product_type_is_aggregated
FROM Products
GROUP BY GROUPING SETS(product_type, product_name)
ORDER BY product_name;

/*--------------+--------------+-------------+----------------------------+
 | product_type | product_name | product_sum | product_type_is_aggregated |
 +--------------+--------------+-------------+----------------------------+
 | shirt        | NULL         | 11          | 0                          |
 | NULL         | NULL         | 25          | 0                          |
 | pants        | NULL         | 6           | 0                          |
 | NULL         | jeans        | 6           | 1                          |
 | NULL         | polo         | 25          | 1                          |
 | NULL         | t-shirt      | 11          | 1                          |
 +--------------+--------------+-------------+----------------------------*/

LOGICAL_AND

LOGICAL_AND(
  expression
)

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.

This function can be used with the AGGREGATION_THRESHOLD clause.

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
)

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.

This function can be used with the AGGREGATION_THRESHOLD clause.

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
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

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.

To learn more about the OVER clause and how to use it, see Window function calls.

This function supports specifying collation.

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  |
 *-----*/

SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 55, NULL, 4]) AS x;

/*------+------*
 | x    | max  |
 +------+------+
 | NULL | NULL |
 | NULL | NULL |
 | 8    | 8    |
 | 4    | 8    |
 | 37   | 55   |
 | 55   | 55   |
 *------+------*/

MAX_BY

MAX_BY(
  x, y
)

Description

Synonym for ANY_VALUE(x HAVING MAX y).

Return Data Types

Matches the input x data type.

Examples

WITH fruits AS (
  SELECT "apple"  fruit, 3.55 price UNION ALL
  SELECT "banana"  fruit, 2.10 price UNION ALL
  SELECT "pear"  fruit, 4.30 price
)
SELECT MAX_BY(fruit, price) as fruit
FROM fruits;

/*-------*
 | fruit |
 +-------+
 | pear  |
 *-------*/

MIN

MIN(
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

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.

To learn more about the OVER clause and how to use it, see Window function calls.

This function supports specifying collation.

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   |
 *-----*/

SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

/*------+------*
 | x    | min  |
 +------+------+
 | NULL | NULL |
 | NULL | NULL |
 | 8    | 4    |
 | 4    | 4    |
 | 37   | 37   |
 | 55   | 37   |
 *------+------*/

MIN_BY

MIN_BY(
  x, y
)

Description

Synonym for ANY_VALUE(x HAVING MIN y).

Return Data Types

Matches the input x data type.

Examples

WITH fruits AS (
  SELECT "apple"  fruit, 3.55 price UNION ALL
  SELECT "banana"  fruit, 2.10 price UNION ALL
  SELECT "pear"  fruit, 4.30 price
)
SELECT MIN_BY(fruit, price) as fruit
FROM fruits;

/*--------*
 | fruit  |
 +--------+
 | banana |
 *--------*/

STRING_AGG

STRING_AGG(
  [ DISTINCT ]
  expression [, delimiter]
  [ ORDER BY key [ { ASC | DESC } ] [, ... ] ]
  [ LIMIT n ]
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

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.

If this function is used with the OVER clause, it's part of a window function call. In a window function call, aggregate function clauses can't be used. To learn more about the OVER clause and how to use it, see Window 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 |
 *-----------------------*/

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

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

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

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

SELECT STRING_AGG(DISTINCT fruit, " & " ORDER BY fruit DESC LIMIT 2) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

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

SELECT
  fruit,
  STRING_AGG(fruit, " & ") OVER (ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

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

SUM

SUM(
  [ DISTINCT ]
  expression
)
[ OVER over_clause ]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

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.

This function can be used with the AGGREGATION_THRESHOLD clause.

To learn more about the OVER clause and how to use it, see Window function calls.

SUM can be used with differential privacy. For more information, see Differentially private aggregate functions.

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
  • INTERVAL

Return Data Types

INPUTINT64NUMERICBIGNUMERICFLOAT64INTERVAL
OUTPUTINT64NUMERICBIGNUMERICFLOAT64INTERVAL

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
  x,
  SUM(x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

/*---+-----*
 | x | sum |
 +---+-----+
 | 3 | 6   |
 | 3 | 6   |
 | 1 | 10  |
 | 4 | 10  |
 | 4 | 10  |
 | 1 | 10  |
 | 2 | 9   |
 | 5 | 9   |
 | 2 | 9   |
 *---+-----*/

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

/*---+-----*
 | x | sum |
 +---+-----+
 | 3 | 3   |
 | 3 | 3   |
 | 1 | 5   |
 | 4 | 5   |
 | 4 | 5   |
 | 1 | 5   |
 | 2 | 7   |
 | 5 | 7   |
 | 2 | 7   |
 *---+-----*/

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

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