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 number 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.

If expression contains any non-NULL values, then ANY_VALUE behaves as if IGNORE NULLS is specified; rows for which expression is NULL aren't 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 .

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

INPUT	`INT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT64`	`INTERVAL`
OUTPUT	`FLOAT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT64`	`INTERVAL`

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`

  COUNT 
 ( 
 * 
 ) 
 [ 
  
 OVER 
  
 over_clause 
  
 ]

  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

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 .
OVER : To learn more, see Aggregate function calls .
over_clause : To learn more, see Aggregate function calls .
window_specification : To learn more, see Window 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.

This function with DISTINCT supports specifying collation .

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

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

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 id s 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 
 ) 
 [ 
  
 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

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 .
OVER : To learn more, see Aggregate function calls .
over_clause : To learn more, see Aggregate function calls .
window_specification : To learn more, see Window 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            | 
 *--------------+--------------*/

  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 
  
 SET 
 S 
 ( 
 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 
  
 SET 
 S 
 ( 
 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 .

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

INPUT	`INT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT64`	`INTERVAL`
OUTPUT	`INT64`	`NUMERIC`	`BIGNUMERIC`	`FLOAT64`	`INTERVAL`

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

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

Function list

ANY_VALUE

ARRAY_AGG

ARRAY_CONCAT_AGG

AVG

BIT_AND

BIT_OR

BIT_XOR

COUNT

COUNTIF

GROUPING

LOGICAL_AND

LOGICAL_OR

MAX

MAX_BY

MIN

MIN_BY

STRING_AGG

SUM

Aggregate functions

`ANY_VALUE`

`ARRAY_AGG`

`ARRAY_CONCAT_AGG`

`AVG`

`BIT_AND`

`BIT_OR`

`BIT_XOR`

`COUNT`

`COUNTIF`

`GROUPING`

`LOGICAL_AND`

`LOGICAL_OR`

`MAX`

`MAX_BY`

`MIN`

`MIN_BY`

`STRING_AGG`

`SUM`