Aggregate functions

GoogleSQL for BigQuery supports the following general aggregate functions.To learn about the syntax for aggregate function calls, seeAggregate 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, seeApproximate aggregate functions.
`APPROX_QUANTILES`	Gets the approximate quantile boundaries. For more information, seeApproximate aggregate functions.
`APPROX_TOP_COUNT`	Gets the approximate top elements and their approximate count. For more information, seeApproximate aggregate functions.
`APPROX_TOP_SUM`	Gets the approximate top elements and sum, based on the approximate sum of an assigned weight. For more information, seeApproximate 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, seeDifferential 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, seeStatistical 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, seeDifferential 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, seeStatistical aggregate functions.
`COVAR_SAMP`	Computes the sample covariance of a set of number pairs. For more information, seeStatistical 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, seeDifferential privacy functions.
`ST_CENTROID_AGG`	Gets the centroid of a set of`GEOGRAPHY` values. For more information, seeGeography functions.
`ST_EXTENT`	Gets the bounding box for a group of`GEOGRAPHY` values. For more information, seeGeography functions.
`ST_UNION_AGG`	Aggregates over`GEOGRAPHY` values and gets their point set union. For more information, seeGeography functions.
`STDDEV`	An alias of the`STDDEV_SAMP` function. For more information, seeStatistical aggregate functions.
`STDDEV_POP`	Computes the population (biased) standard deviation of the values. For more information, seeStatistical aggregate functions.
`STDDEV_SAMP`	Computes the sample (unbiased) standard deviation of the values. For more information, seeStatistical aggregate functions.
`STRING_AGG`	Concatenates non-`NULLSTRING` 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, seeDifferential privacy functions.
`VAR_POP`	Computes the population (biased) variance of the values. For more information, seeStatistical aggregate functions.
`VAR_SAMP`	Computes the sample (unbiased) variance of the values. For more information, seeStatistical aggregate functions.
`VARIANCE`	An alias of`VAR_SAMP`. For more information, seeStatistical aggregate functions.

`ANY_VALUE`

ANY_VALUE(expression[HAVING{MAX|MIN}having_expression])[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]

Description

Returnsexpression for some row chosen from the group. Which row is chosen isnondeterministic, not random. ReturnsNULL when the input produces norows. ReturnsNULL whenexpressionorhaving_expression isNULL for all rows in the group.

Ifexpression contains any non-NULL values, thenANY_VALUE behaves as ifIGNORE NULLS is specified;rows for whichexpression isNULL aren't considered and won't beselected.

If theHAVING clause is included in theANY_VALUE function, theOVER clause can't be used with this function.

To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate function calls.

To learn more about theOVER clause and how to use it, seeWindow function calls.

Supported Argument Types

Any

Returned Data Types

Matches the input data type.

Examples

SELECTANY_VALUE(fruit)asany_valueFROMUNNEST(["apple","banana","pear"])asfruit;/*-----------+ | any_value | +-----------+ | apple     | +-----------*/

SELECTfruit,ANY_VALUE(fruit)OVER(ORDERBYLENGTH(fruit)ROWSBETWEEN1PRECEDINGANDCURRENTROW)ASany_valueFROMUNNEST(["apple","banana","pear"])asfruit;/*--------+-----------+ | fruit  | any_value | +--------+-----------+ | pear   | pear      | | apple  | pear      | | banana | apple     | +--------+-----------*/

WITHStoreAS(SELECT20ASsold,"apples"ASfruitUNIONALLSELECT30ASsold,"pears"ASfruitUNIONALLSELECT30ASsold,"bananas"ASfruitUNIONALLSELECT10ASsold,"oranges"ASfruit)SELECTANY_VALUE(fruitHAVINGMAXsold)ASa_highest_selling_fruitFROMStore;/*-------------------------+ | a_highest_selling_fruit | +-------------------------+ | pears                   | +-------------------------*/

WITHStoreAS(SELECT20ASsold,"apples"ASfruitUNIONALLSELECT30ASsold,"pears"ASfruitUNIONALLSELECT30ASsold,"bananas"ASfruitUNIONALLSELECT10ASsold,"oranges"ASfruit)SELECTANY_VALUE(fruitHAVINGMINsold)ASa_lowest_selling_fruitFROMStore;/*-------------------------+ | a_lowest_selling_fruit  | +-------------------------+ | oranges                 | +-------------------------*/

`ARRAY_AGG`

ARRAY_AGG([DISTINCT]expression[{IGNORE|RESPECT}NULLS][ORDERBYkey[{ASC|DESC}][,...]][LIMITn])[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]

Description

Returns an ARRAY ofexpression values.

To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate function calls.

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

An error is raised if an array in the final query result contains aNULLelement.

Supported Argument Types

All data types except ARRAY.

Returned Data Types

ARRAY

If there are zero input rows, this function returnsNULL.

Examples

SELECTARRAY_AGG(x)ASarray_aggFROMUNNEST([2,1,-2,3,-2,1,2])ASx;/*-------------------------+ | array_agg               | +-------------------------+ | [2, 1, -2, 3, -2, 1, 2] | +-------------------------*/

SELECTARRAY_AGG(DISTINCTx)ASarray_aggFROMUNNEST([2,1,-2,3,-2,1,2])ASx;/*---------------+ | array_agg     | +---------------+ | [2, 1, -2, 3] | +---------------*/

SELECTARRAY_AGG(xIGNORENULLS)ASarray_aggFROMUNNEST([NULL,1,-2,3,-2,1,NULL])ASx;/*-------------------+ | array_agg         | +-------------------+ | [1, -2, 3, -2, 1] | +-------------------*/

SELECTARRAY_AGG(xORDERBYABS(x))ASarray_aggFROMUNNEST([2,1,-2,3,-2,1,2])ASx;/*-------------------------+ | array_agg               | +-------------------------+ | [1, 1, 2, -2, -2, 2, 3] | +-------------------------*/

SELECTARRAY_AGG(xLIMIT5)ASarray_aggFROMUNNEST([2,1,-2,3,-2,1,2])ASx;/*-------------------+ | array_agg         | +-------------------+ | [2, 1, -2, 3, -2] | +-------------------*/

WITHvalsAS(SELECT1xUNIONALLSELECT-2xUNIONALLSELECT3xUNIONALLSELECT-2xUNIONALLSELECT1x)SELECTARRAY_AGG(DISTINCTxORDERBYx)asarray_aggFROMvals;/*------------+ | array_agg  | +------------+ | [-2, 1, 3] | +------------*/

WITHvalsAS(SELECT1x,'a'yUNIONALLSELECT1x,'b'yUNIONALLSELECT2x,'a'yUNIONALLSELECT2x,'c'y)SELECTx,ARRAY_AGG(y)asarray_aggFROMvalsGROUPBYx;/*---------------+ | x | array_agg | +---------------+ | 1 | [a, b]    | | 2 | [a, c]    | +---------------*/

SELECTx,ARRAY_AGG(x)OVER(ORDERBYABS(x))ASarray_aggFROMUNNEST([2,1,-2,3,-2,1,2])ASx;/*----+-------------------------+ | 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[ORDERBYkey[{ASC|DESC}][,...]][LIMITn])

Description

Concatenates elements fromexpression of typeARRAY, returning a singlearray as a result.

This function ignoresNULL input arrays, but respects theNULL elements innon-NULL input arrays. Anerror is raised, however, if an array in the final query result contains aNULL element. ReturnsNULL if there are zero input rows orexpression evaluates toNULL for all rows.

To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate function calls.

Supported Argument Types

ARRAY

Returned Data Types

ARRAY

Examples

SELECTFORMAT("%T",ARRAY_CONCAT_AGG(x))ASarray_concat_aggFROM(SELECT[NULL,1,2,3,4]ASxUNIONALLSELECTNULLUNIONALLSELECT[5,6]UNIONALLSELECT[7,8,9]);/*-----------------------------------+ | array_concat_agg                  | +-----------------------------------+ | [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] | +-----------------------------------*/

SELECTFORMAT("%T",ARRAY_CONCAT_AGG(xORDERBYARRAY_LENGTH(x)))ASarray_concat_aggFROM(SELECT[1,2,3,4]ASxUNIONALLSELECT[5,6]UNIONALLSELECT[7,8,9]);/*-----------------------------------+ | array_concat_agg                  | +-----------------------------------+ | [5, 6, 7, 8, 9, 1, 2, 3, 4]       | +-----------------------------------*/

SELECTFORMAT("%T",ARRAY_CONCAT_AGG(xLIMIT2))ASarray_concat_aggFROM(SELECT[1,2,3,4]ASxUNIONALLSELECT[5,6]UNIONALLSELECT[7,8,9]);/*--------------------------+ | array_concat_agg         | +--------------------------+ | [1, 2, 3, 4, 5, 6]       | +--------------------------*/

SELECTFORMAT("%T",ARRAY_CONCAT_AGG(xORDERBYARRAY_LENGTH(x)LIMIT2))ASarray_concat_aggFROM(SELECT[1,2,3,4]ASxUNIONALLSELECT[5,6]UNIONALLSELECT[7,8,9]);/*------------------+ | array_concat_agg | +------------------+ | [5, 6, 7, 8, 9]  | +------------------*/

`AVG`

AVG([DISTINCT]expression)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{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 passinto this function, seeAggregate function calls.

This function can be used with theAGGREGATION_THRESHOLD clause.

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

Caveats:

If the aggregated group is empty or the argument isNULL for all rows inthe group, returnsNULL.
If the argument isNaN for any row in the group, returnsNaN.
If the argument is[+|-]Infinity for any row in the group, returns either[+|-]Infinity orNaN.
If there is numeric overflow, produces an error.
If afloating-point type is returned, the result isnon-deterministic, which means you might receive adifferent 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

SELECTAVG(x)asavgFROMUNNEST([0,2,4,4,5])asx;/*-----+ | avg | +-----+ | 3   | +-----*/

SELECTAVG(DISTINCTx)ASavgFROMUNNEST([0,2,4,4,5])ASx;/*------+ | avg  | +------+ | 2.75 | +------*/

SELECTx,AVG(x)OVER(ORDERBYxROWSBETWEEN1PRECEDINGANDCURRENTROW)ASavgFROMUNNEST([0,2,NULL,4,4,5])ASx;/*------+------+ | 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 onexpression and returns the result.

To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate function calls.

Supported Argument Types

INT64

Returned Data Types

INT64

Examples

SELECTBIT_AND(x)asbit_andFROMUNNEST([0xF001,0x00A1])asx;/*---------+ | bit_and | +---------+ | 1       | +---------*/

`BIT_OR`

BIT_OR(expression)

Description

Performs a bitwise OR operation onexpression and returns the result.

To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate function calls.

Supported Argument Types

INT64

Returned Data Types

INT64

Examples

SELECTBIT_OR(x)asbit_orFROMUNNEST([0xF001,0x00A1])asx;/*--------+ | bit_or | +--------+ | 61601  | +--------*/

`BIT_XOR`

BIT_XOR([DISTINCT]expression)

Description

Performs a bitwise XOR operation onexpression and returns the result.

To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate function calls.

Supported Argument Types

INT64

Returned Data Types

INT64

Examples

SELECTBIT_XOR(x)ASbit_xorFROMUNNEST([5678,1234])ASx;/*---------+ | bit_xor | +---------+ | 4860    | +---------*/

SELECTBIT_XOR(x)ASbit_xorFROMUNNEST([1234,5678,1234])ASx;/*---------+ | bit_xor | +---------+ | 5678    | +---------*/

SELECTBIT_XOR(DISTINCTx)ASbit_xorFROMUNNEST([1234,5678,1234])ASx;/*---------+ | bit_xor | +---------+ | 4860    | +---------*/

`COUNT`

COUNT(*)[OVERover_clause]

COUNT([DISTINCT]expression)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]

Description

Gets the number of rows in the input or the number of rows with anexpression evaluated to any value other thanNULL.

Note: If you're querying a large dataset, you can compute results faster andsave resources by using HLL++ functions for approximatedistinct counts. For more information, seeSketches.

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 toevaluate. IfDISTINCT is present,expression can only be a data type that isgroupable.
DISTINCT: To learn more, seeAggregate function calls.
OVER: To learn more, seeAggregate function calls.
over_clause: To learn more, seeAggregate function calls.
window_specification: To learn more, seeWindow function calls.

Details

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

COUNT(DISTINCTIF(condition,expression,NULL))

IF returns the value ofexpression ifcondition isTRUE, orNULL otherwise. The surroundingCOUNT(DISTINCT ...) ignores theNULLvalues, so it counts only the distinct values ofexpression for whichcondition isTRUE.

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

This function withDISTINCT supports specifyingcollation.

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

Return type

INT64

Examples

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

SELECTCOUNT(*)AScount_star,COUNT(DISTINCTx)AScount_dist_xFROMUNNEST([1,4,4,5])ASx;/*------------+--------------+ | count_star | count_dist_x | +------------+--------------+ | 4          | 3            | +------------+--------------*/

SELECTx,COUNT(*)OVER(PARTITIONBYMOD(x,3))AScount_star,COUNT(DISTINCTx)OVER(PARTITIONBYMOD(x,3))AScount_dist_xFROMUNNEST([1,4,4,5])ASx;/*------+------------+--------------+ | x    | count_star | count_dist_x | +------+------------+--------------+ | 1    | 3          | 2            | | 4    | 3          | 2            | | 4    | 3          | 2            | | 5    | 1          | 1            | +------+------------+--------------*/

SELECTx,COUNT(*)OVER(PARTITIONBYMOD(x,3))AScount_star,COUNT(x)OVER(PARTITIONBYMOD(x,3))AScount_xFROMUNNEST([1,4,NULL,4,5])ASx;/*------+------------+---------+ | 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 ofx:

SELECTCOUNT(DISTINCTIF(x >0,x,NULL))ASdistinct_positiveFROMUNNEST([1,-2,4,1,-5,4,1,3,-6,1])ASx;/*-------------------+ | distinct_positive | +-------------------+ | 3                 | +-------------------*/

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

WITHEventsAS(SELECTDATE'2021-01-01'ASevent_date,'SUCCESS'ASevent_typeUNIONALLSELECTDATE'2021-01-02'ASevent_date,'SUCCESS'ASevent_typeUNIONALLSELECTDATE'2021-01-02'ASevent_date,'FAILURE'ASevent_typeUNIONALLSELECTDATE'2021-01-03'ASevent_date,'SUCCESS'ASevent_typeUNIONALLSELECTDATE'2021-01-04'ASevent_date,'FAILURE'ASevent_typeUNIONALLSELECTDATE'2021-01-04'ASevent_date,'FAILURE'ASevent_type)SELECTCOUNT(DISTINCTIF(event_type='FAILURE',event_date,NULL))ASdistinct_dates_with_failuresFROMEvents;/*------------------------------+ | distinct_dates_with_failures | +------------------------------+ | 2                            | +------------------------------*/

The following query counts the number of distinctids that exist in boththecustomers andvendor tables:

WITHcustomersAS(SELECT1934ASid,'a'ASteamUNIONALLSELECT2991,'b'UNIONALLSELECT3988,'c'),vendorsAS(SELECT1934ASid,'d'ASteamUNIONALLSELECT2991,'e'UNIONALLSELECT4366,'f')SELECTCOUNT(DISTINCTIF(idIN(SELECTidFROMcustomers),id,NULL))ASresultFROMvendors;/*--------+ | result | +--------+ | 2      | +--------*/

`COUNTIF`

COUNTIF([DISTINCT]expression)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]

Description

Gets the number ofTRUE values for an expression.

Definitions

expression: ABOOL value that represents the expression to evaluate.
DISTINCT: To learn more, seeAggregate function calls.
OVER: To learn more, seeAggregate function calls.
over_clause: To learn more, seeAggregate function calls.
window_specification: To learn more, seeWindow function calls.

Details

The function signatureCOUNTIF(DISTINCT ...) is generally not useful. If youwould like to useDISTINCT, useCOUNT withDISTINCT IF. For moreinformation, see theCOUNT function.

Return type

INT64

Examples

SELECTCOUNTIF(x<0)ASnum_negative,COUNTIF(x>0)ASnum_positiveFROMUNNEST([5,-2,3,6,-10,-7,4,0])ASx;/*--------------+--------------+ | num_negative | num_positive | +--------------+--------------+ | 3            | 4            | +--------------+--------------*/

SELECTx,COUNTIF(x<0)OVER(ORDERBYABS(x)ROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASnum_negativeFROMUNNEST([5,-2,3,6,-10,NULL,-7,4,0])ASx;/*------+--------------+ | 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 theGROUP BY clause is aggregated(and thus not grouped), this function returns1. Otherwise,this function returns0.

Definitions:

groupable_value: An expression that represents a value that can be groupedin theGROUP BY clause.

Details:

TheGROUPING function is helpful if you need to determine which rows areproduced by which grouping sets. A grouping set is a group of columns by whichrows can be grouped together. So, if you need to filter rows bya few specific grouping sets, you can use theGROUPING function to identifywhich grouping sets grouped which rows by creating a matrix of the results.

In addition, you can use theGROUPING function to determine the type ofNULL produced by theGROUP BY clause. In some cases, theGROUP BY clauseproduces aNULL placeholder. This placeholder represents all groupable itemsthat are aggregated (not grouped) in the current grouping set. This is differentfrom a standardNULL, 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 bythe grouping valueproduct_type orproduct_name. TheGROUPING functionmakes this easier to determine.

Pay close attention to what's in theproduct_type_agg andproduct_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.

WITHProductsAS(SELECT'shirt'ASproduct_type,'t-shirt'ASproduct_name,3ASproduct_countUNIONALLSELECT'shirt','t-shirt',8UNIONALLSELECT'shirt','polo',25UNIONALLSELECT'pants','jeans',6)SELECTproduct_type,product_name,SUM(product_count)ASproduct_sum,GROUPING(product_type)ASproduct_type_agg,GROUPING(product_name)ASproduct_name_agg,FROMProductsGROUPBYGROUPINGSETS(product_type,product_name,())ORDERBYproduct_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 determineifNULL represents aNULL placeholder or a standardNULL value in theproduct_type column. TheGROUPING function makes it easier todetermine what type ofNULL is being produced. Ifproduct_type_is_aggregated is1, theNULL value fortheproduct_type column is aNULL placeholder.

WITHProductsAS(SELECT'shirt'ASproduct_type,'t-shirt'ASproduct_name,3ASproduct_countUNIONALLSELECT'shirt','t-shirt',8UNIONALLSELECTNULL,'polo',25UNIONALLSELECT'pants','jeans',6)SELECTproduct_type,product_name,SUM(product_count)ASproduct_sum,GROUPING(product_type)ASproduct_type_is_aggregatedFROMProductsGROUPBYGROUPINGSETS(product_type,product_name)ORDERBYproduct_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. ReturnsNULL if thereare zero input rows orexpression evaluates toNULL for all rows.

To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate function calls.

This function can be used with theAGGREGATION_THRESHOLD clause.

Supported Argument Types

BOOL

Return Data Types

BOOL

Examples

LOGICAL_AND returnsFALSE because not all of the values in the array areless than 3.

SELECTLOGICAL_AND(x <3)ASlogical_andFROMUNNEST([1,2,4])ASx;/*-------------+ | logical_and | +-------------+ | FALSE       | +-------------*/

`LOGICAL_OR`

LOGICAL_OR(expression)

Description

Returns the logical OR of all non-NULL expressions. ReturnsNULL if thereare zero input rows orexpression evaluates toNULL for all rows.

To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate function calls.

This function can be used with theAGGREGATION_THRESHOLD clause.

Supported Argument Types

BOOL

Return Data Types

BOOL

Examples

LOGICAL_OR returnsTRUE because at least one of the values in the array isless than 3.

SELECTLOGICAL_OR(x <3)ASlogical_orFROMUNNEST([1,2,4])ASx;/*------------+ | logical_or | +------------+ | TRUE       | +------------*/

`MAX`

MAX(expression)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{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 isNULL for all rows inthe group, returnsNULL.
If the argument isNaN for any row in the group, returnsNaN.

To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate function calls.

To learn more about theOVER clause and how to use it, seeWindow function calls.

This function supports specifyingcollation.

Supported Argument Types

Anyorderable data type except forARRAY.

Return Data Types

The data type of the input values.

Examples

SELECTMAX(x)ASmaxFROMUNNEST([8,37,55,4])ASx;/*-----+ | max | +-----+ | 55  | +-----*/

SELECTx,MAX(x)OVER(PARTITIONBYMOD(x,2))ASmaxFROMUNNEST([8,NULL,37,55,NULL,4])ASx;/*------+------+ | x    | max  | +------+------+ | NULL | NULL | | NULL | NULL | | 8    | 8    | | 4    | 8    | | 37   | 55   | | 55   | 55   | +------+------*/

`MAX_BY`

MAX_BY(x,y)

Description

Synonym forANY_VALUE(x HAVING MAX y).

Return Data Types

Matches the inputx data type.

Examples

WITHfruitsAS(SELECT"apple"fruit,3.55priceUNIONALLSELECT"banana"fruit,2.10priceUNIONALLSELECT"pear"fruit,4.30price)SELECTMAX_BY(fruit,price)asfruitFROMfruits;/*-------+ | fruit | +-------+ | pear  | +-------*/

`MIN`

MIN(expression)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{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 isNULL for all rows inthe group, returnsNULL.
If the argument isNaN for any row in the group, returnsNaN.

To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate function calls.

To learn more about theOVER clause and how to use it, seeWindow function calls.

This function supports specifyingcollation.

Supported Argument Types

Anyorderable data type except forARRAY.

Return Data Types

The data type of the input values.

Examples

SELECTMIN(x)ASminFROMUNNEST([8,37,4,55])ASx;/*-----+ | min | +-----+ | 4   | +-----*/

SELECTx,MIN(x)OVER(PARTITIONBYMOD(x,2))ASminFROMUNNEST([8,NULL,37,4,NULL,55])ASx;/*------+------+ | x    | min  | +------+------+ | NULL | NULL | | NULL | NULL | | 8    | 4    | | 4    | 4    | | 37   | 37   | | 55   | 37   | +------+------*/

`MIN_BY`

MIN_BY(x,y)

Description

Synonym forANY_VALUE(x HAVING MIN y).

Return Data Types

Matches the inputx data type.

Examples

WITHfruitsAS(SELECT"apple"fruit,3.55priceUNIONALLSELECT"banana"fruit,2.10priceUNIONALLSELECT"pear"fruit,4.30price)SELECTMIN_BY(fruit,price)asfruitFROMfruits;/*--------+ | fruit  | +--------+ | banana | +--------*/

`STRING_AGG`

STRING_AGG([DISTINCT]expression[,delimiter][ORDERBYkey[{ASC|DESC}][,...]][LIMITn])[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]

Description

Returns a value (eitherSTRING orBYTES) obtained by concatenatingnon-NULL values. ReturnsNULL if there are zero input rows orexpressionevaluates toNULL for all rows.

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

To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate function calls.

Supported Argument Types

EitherSTRING orBYTES.

Return Data Types

EitherSTRING orBYTES.

Examples

SELECTSTRING_AGG(fruit)ASstring_aggFROMUNNEST(["apple",NULL,"pear","banana","pear"])ASfruit;/*------------------------+ | string_agg             | +------------------------+ | apple,pear,banana,pear | +------------------------*/

SELECTSTRING_AGG(fruit," & ")ASstring_aggFROMUNNEST(["apple","pear","banana","pear"])ASfruit;/*------------------------------+ | string_agg                   | +------------------------------+ | apple & pear & banana & pear | +------------------------------*/

SELECTSTRING_AGG(DISTINCTfruit," & ")ASstring_aggFROMUNNEST(["apple","pear","banana","pear"])ASfruit;/*-----------------------+ | string_agg            | +-----------------------+ | apple & pear & banana | +-----------------------*/

SELECTSTRING_AGG(fruit," & "ORDERBYLENGTH(fruit))ASstring_aggFROMUNNEST(["apple","pear","banana","pear"])ASfruit;/*------------------------------+ | string_agg                   | +------------------------------+ | pear & pear & apple & banana | +------------------------------*/

SELECTSTRING_AGG(fruit," & "LIMIT2)ASstring_aggFROMUNNEST(["apple","pear","banana","pear"])ASfruit;/*--------------+ | string_agg   | +--------------+ | apple & pear | +--------------*/

SELECTSTRING_AGG(DISTINCTfruit," & "ORDERBYfruitDESCLIMIT2)ASstring_aggFROMUNNEST(["apple","pear","banana","pear"])ASfruit;/*---------------+ | string_agg    | +---------------+ | pear & banana | +---------------*/

SELECTfruit,STRING_AGG(fruit," & ")OVER(ORDERBYLENGTH(fruit))ASstring_aggFROMUNNEST(["apple",NULL,"pear","banana","pear"])ASfruit;/*--------+------------------------------+ | fruit  | string_agg                   | +--------+------------------------------+ | NULL   | NULL                         | | pear   | pear & pear                  | | pear   | pear & pear                  | | apple  | pear & pear & apple          | | banana | pear & pear & apple & banana | +--------+------------------------------*/

`SUM`

SUM([DISTINCT]expression)[OVERover_clause]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{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 passinto this function, seeAggregate function calls.

This function can be used with theAGGREGATION_THRESHOLD clause.

To learn more about theOVER clause and how to use it, seeWindow function calls.

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

Caveats:

If the aggregated group is empty or the argument isNULL for all rows inthe group, returnsNULL.
If the argument isNaN for any row in the group, returnsNaN.
If the argument is[+|-]Infinity for any row in the group, returns either[+|-]Infinity orNaN.
If there is numeric overflow, produces an error.
If afloating-point type is returned, the result isnon-deterministic, which means you might receive adifferent 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

SELECTSUM(x)ASsumFROMUNNEST([1,2,3,4,5,4,3,2,1])ASx;/*-----+ | sum | +-----+ | 25  | +-----*/

SELECTSUM(DISTINCTx)ASsumFROMUNNEST([1,2,3,4,5,4,3,2,1])ASx;/*-----+ | sum | +-----+ | 15  | +-----*/

SELECTx,SUM(x)OVER(PARTITIONBYMOD(x,3))ASsumFROMUNNEST([1,2,3,4,5,4,3,2,1])ASx;/*---+-----+ | x | sum | +---+-----+ | 3 | 6   | | 3 | 6   | | 1 | 10  | | 4 | 10  | | 4 | 10  | | 1 | 10  | | 2 | 9   | | 5 | 9   | | 2 | 9   | +---+-----*/

SELECTx,SUM(DISTINCTx)OVER(PARTITIONBYMOD(x,3))ASsumFROMUNNEST([1,2,3,4,5,4,3,2,1])ASx;/*---+-----+ | x | sum | +---+-----+ | 3 | 3   | | 3 | 3   | | 1 | 5   | | 4 | 5   | | 4 | 5   | | 1 | 5   | | 2 | 7   | | 5 | 7   | | 2 | 7   | +---+-----*/

SELECTSUM(x)ASsumFROMUNNEST([])ASx;/*------+ | sum  | +------+ | NULL | +------*/

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-11-24 UTC.

Movatterモバイル変換

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`