Aggregate functions in GoogleSQL

GoogleSQL for Spanner 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.
`ARRAY_AGG`	Gets an array of values.
`ARRAY_CONCAT_AGG`	Concatenates arrays and returns a single array as a result.
`AVG`	Gets the average of non-`NULL` values.
`BIT_AND`	Performs a bitwise AND operation on an expression.
`BIT_OR`	Performs a bitwise OR operation on an expression.
`BIT_XOR`	Performs a bitwise XOR operation on an expression.
`COUNT`	Gets the number of rows in the input, or the number of rows with an expression evaluated to any value other than`NULL`.
`COUNTIF`	Gets the number of`TRUE` values for an expression.
`LOGICAL_AND`	Gets the logical AND of all non-`NULL` expressions.
`LOGICAL_OR`	Gets the logical OR of all non-`NULL` expressions.
`MAX`	Gets the maximum non-`NULL` value.
`MIN`	Gets the minimum non-`NULL` value.
`STDDEV`	An alias of the`STDDEV_SAMP` function. For more information, 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.
`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])

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.

To learn more about the optional aggregate clauses that you can passinto this function, seeAggregate 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     | +-----------*/

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][HAVING{MAX|MIN}having_expression][ORDERBYkey[{ASC|DESC}][,...]][LIMITn])

Description

Returns an ARRAY ofexpression values.

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

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

`ARRAY_CONCAT_AGG`

ARRAY_CONCAT_AGG(expression[HAVING{MAX|MIN}having_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. 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

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

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

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

SELECTARRAY_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[HAVING{MAX|MIN}having_expression])

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.

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`	`FLOAT32`	`FLOAT64`	`INTERVAL`
OUTPUT	`FLOAT64`	`NUMERIC`	`FLOAT64`	`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 | +------*/

`BIT_AND`

BIT_AND([DISTINCT]expression[HAVING{MAX|MIN}having_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([DISTINCT]expression[HAVING{MAX|MIN}having_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[HAVING{MAX|MIN}having_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(*)

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

Description

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

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.
HAVING { MAX | MIN }: To learn more, seeAggregate 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.

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

SELECTCOUNT(*)AScount_star,COUNT(x)AScount_xFROMUNNEST([1,4,NULL,4,5])ASx;/*------------+---------+ | count_star | count_x | +------------+---------+ | 5          | 4       | +------------+---------*/

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[HAVING{MAX|MIN}having_expression])

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.
HAVING { MAX | MIN }: To learn more, seeAggregate 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            | +--------------+--------------*/

`LOGICAL_AND`

LOGICAL_AND(expression[HAVING{MAX|MIN}having_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.

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[HAVING{MAX|MIN}having_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.

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[HAVING{MAX|MIN}having_expression])

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.

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

`MIN`

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

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.

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

`STRING_AGG`

STRING_AGG([DISTINCT]expression[,delimiter][HAVING{MAX|MIN}having_expression][ORDERBYkey[{ASC|DESC}][,...]][LIMITn])

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

`SUM`

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

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.

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`	`FLOAT32`	`FLOAT64`	`INTERVAL`
OUTPUT	`INT64`	`NUMERIC`	`FLOAT64`	`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  | +-----*/

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-12-15 UTC.

Movatterモバイル変換

Aggregate functions in GoogleSQL 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

LOGICAL_AND

LOGICAL_OR

MAX

MIN

STRING_AGG

SUM

Aggregate functions in GoogleSQL

`ANY_VALUE`

`ARRAY_AGG`

`ARRAY_CONCAT_AGG`

`AVG`

`BIT_AND`

`BIT_OR`

`BIT_XOR`

`COUNT`

`COUNTIF`

`LOGICAL_AND`

`LOGICAL_OR`

`MAX`

`MIN`

`STRING_AGG`

`SUM`