The following general aggregate functions are available in Google Standard SQL. To learn about the syntax for aggregate function calls, see Aggregate function calls.
ANY_VALUE
ANY_VALUE(
expression
[ HAVING { MAX | MIN } expression2 ]
)
Description
Returns expression
for some row chosen from the group. Which row is chosen is
nondeterministic, not random. Returns NULL
when the input produces no
rows. Returns NULL
when expression
is NULL
for all rows in the group.
ANY_VALUE
behaves as if IGNORE NULLS
is specified;
rows for which expression
is NULL
are not considered and won't be
selected.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
Any
Returned Data Types
Matches the input data type.
Examples
SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;
+-----------+
| any_value |
+-----------+
| apple |
+-----------+
ARRAY_AGG
ARRAY_AGG(
[ DISTINCT ]
expression
[ { IGNORE | RESPECT } NULLS ]
[ HAVING { MAX | MIN } expression2 ]
)
Description
Returns an ARRAY of expression
values.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
All data types except ARRAY.
Returned Data Types
ARRAY
If there are zero input rows, this function returns NULL
.
Examples
SELECT ARRAY_AGG(x) AS array_agg FROM UNNEST([2, 1,-2, 3, -2, 1, 2]) AS x;
+-------------------------+
| array_agg |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT ARRAY_AGG(DISTINCT x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;
+---------------+
| array_agg |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT ARRAY_AGG(x IGNORE NULLS) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;
+-------------------+
| array_agg |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+
WITH vals AS
(
SELECT 1 x, 'a' y UNION ALL
SELECT 1 x, 'b' y UNION ALL
SELECT 2 x, 'a' y UNION ALL
SELECT 2 x, 'c' y
)
SELECT x, ARRAY_AGG(y) as array_agg
FROM vals
GROUP BY x;
+---------------+
| x | array_agg |
+---------------+
| 1 | [a, b] |
| 2 | [a, c] |
+---------------+
ARRAY_CONCAT_AGG
ARRAY_CONCAT_AGG(
expression
[ HAVING { MAX | MIN } expression2 ]
)
Description
Concatenates elements from expression
of type
ARRAY, returning a single
ARRAY as a result. This function ignores NULL input
arrays, but respects the NULL elements in non-NULL input arrays.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
ARRAY
Returned Data Types
ARRAY
Returns NULL
if there are zero input
rows or expression
evaluates to NULL for all rows.
Examples
SELECT ARRAY_CONCAT_AGG(x) AS array_concat_agg FROM (
SELECT [NULL, 1, 2, 3, 4] AS x
UNION ALL SELECT NULL
UNION ALL SELECT [5, 6]
UNION ALL SELECT [7, 8, 9]
);
+-----------------------------------+
| array_concat_agg |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+
AVG
AVG(
[ DISTINCT ]
expression
[ HAVING { MAX | MIN } expression2 ]
)
Description
Returns the average of non-NULL
input values, or NaN
if the input contains a
NaN
.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
Any numeric input type, such as INT64. Note that, for floating point input types, the return result is non-deterministic, which means you might receive a different result each time you use this function.
Returned Data Types
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | FLOAT64 |
Examples
SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;
+-----+
| avg |
+-----+
| 3 |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;
+------+
| avg |
+------+
| 2.75 |
+------+
BIT_AND
BIT_AND(
[ DISTINCT ]
expression
[ HAVING { MAX | MIN } expression2 ]
)
Description
Performs a bitwise AND operation on expression
and returns the result.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
- INT64
Returned Data Types
INT64
Examples
SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;
+---------+
| bit_and |
+---------+
| 1 |
+---------+
BIT_OR
BIT_OR(
[ DISTINCT ]
expression
[ HAVING { MAX | MIN } expression2 ]
)
Description
Performs a bitwise OR operation on expression
and returns the result.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
- INT64
Returned Data Types
INT64
Examples
SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;
+--------+
| bit_or |
+--------+
| 61601 |
+--------+
BIT_XOR
BIT_XOR(
[ DISTINCT ]
expression
[ HAVING { MAX | MIN } expression2 ]
)
Description
Performs a bitwise XOR operation on expression
and returns the result.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
- INT64
Returned Data Types
INT64
Examples
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 4860 |
+---------+
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 5678 |
+---------+
SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 4860 |
+---------+
COUNT
1.
COUNT(*)
2.
COUNT(
[ DISTINCT ]
expression
[ HAVING { MAX | MIN } expression2 ]
)
Description
- Returns the number of rows in the input.
- Returns the number of rows with
expression
evaluated to any value other thanNULL
.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
expression
can be any data type. If
DISTINCT
is present, expression
can only be a data type that is
groupable.
Return Data Types
INT64
Examples
You can use the COUNT
function to return the number of rows in a table or the
number of distinct values of an expression. For example:
SELECT
COUNT(*) AS count_star,
COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;
+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4 | 3 |
+------------+--------------+
SELECT COUNT(*) AS count_star, COUNT(x) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;
+------------+---------+
| count_star | count_x |
+------------+---------+
| 5 | 4 |
+------------+---------+
If you want to count the number of distinct values of an expression for which a certain condition is satisfied, this is one recipe that you can use:
COUNT(DISTINCT IF(condition, expression, NULL))
Here, IF
will return the value of expression
if condition
is TRUE
, or
NULL
otherwise. The surrounding COUNT(DISTINCT ...)
will ignore the NULL
values, so it will count only the distinct values of expression
for which
condition
is TRUE
.
For example, to count the number of distinct positive values of x
:
SELECT COUNT(DISTINCT IF(x > 0, x, NULL)) AS distinct_positive
FROM UNNEST([1, -2, 4, 1, -5, 4, 1, 3, -6, 1]) AS x;
+-------------------+
| distinct_positive |
+-------------------+
| 3 |
+-------------------+
Or to count the number of distinct dates on which a certain kind of event occurred:
WITH Events AS (
SELECT DATE '2021-01-01' AS event_date, 'SUCCESS' AS event_type
UNION ALL
SELECT DATE '2021-01-02' AS event_date, 'SUCCESS' AS event_type
UNION ALL
SELECT DATE '2021-01-02' AS event_date, 'FAILURE' AS event_type
UNION ALL
SELECT DATE '2021-01-03' AS event_date, 'SUCCESS' AS event_type
UNION ALL
SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
UNION ALL
SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
)
SELECT
COUNT(DISTINCT IF(event_type = 'FAILURE', event_date, NULL))
AS distinct_dates_with_failures
FROM Events;
+------------------------------+
| distinct_dates_with_failures |
+------------------------------+
| 2 |
+------------------------------+
COUNTIF
COUNTIF(
[ DISTINCT ]
expression
[ HAVING { MAX | MIN } expression2 ]
)
Description
Returns the count of TRUE
values for expression
. Returns 0
if there are
zero input rows, or if expression
evaluates to FALSE
or NULL
for all rows.
Since expression
must be a BOOL
, the form
COUNTIF(DISTINCT ...)
is generally not useful: there is only one distinct
value of TRUE
. So COUNTIF(DISTINCT ...)
will return 1 if expression
evaluates to TRUE
for one or more input rows, or 0 otherwise.
Usually when someone wants to combine COUNTIF
and DISTINCT
, they
want to count the number of distinct values of an expression for which a certain
condition is satisfied. One recipe to achieve this is the following:
COUNT(DISTINCT IF(condition, expression, NULL))
Note that this uses COUNT
, not COUNTIF
; the IF
part has been moved inside.
To learn more, see the examples for COUNT
.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
BOOL
Return Data Types
INT64
Examples
SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;
+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3 | 4 |
+--------------+--------------+
LOGICAL_AND
LOGICAL_AND(
expression
[ HAVING { MAX | MIN } expression2 ]
)
Description
Returns the logical AND of all non-NULL
expressions. Returns NULL
if there
are zero input rows or expression
evaluates to NULL
for all rows.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
BOOL
Return Data Types
BOOL
Examples
SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;
+-------------+
| logical_and |
+-------------+
| false |
+-------------+
LOGICAL_OR
LOGICAL_OR(
expression
[ HAVING { MAX | MIN } expression2 ]
)
Description
Returns the logical OR of all non-NULL
expressions. Returns NULL
if there
are zero input rows or expression
evaluates to NULL
for all rows.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
BOOL
Return Data Types
BOOL
Examples
SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;
+------------+
| logical_or |
+------------+
| true |
+------------+
MAX
MAX(
expression
[ HAVING { MAX | MIN } expression2 ]
)
Description
Returns the maximum value of non-NULL
expressions. Returns NULL
if there
are zero input rows or expression
evaluates to NULL
for all rows.
Returns NaN
if the input contains a NaN
.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
Any orderable data type.
Return Data Types
Same as the data type used as the input values.
Examples
SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;
+-----+
| max |
+-----+
| 55 |
+-----+
MIN
MIN(
expression
[ HAVING { MAX | MIN } expression2 ]
)
Description
Returns the minimum value of non-NULL
expressions. Returns NULL
if there
are zero input rows or expression
evaluates to NULL
for all rows.
Returns NaN
if the input contains a NaN
.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
Any orderable data type.
Return Data Types
Same as the data type used as the input values.
Examples
SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;
+-----+
| min |
+-----+
| 4 |
+-----+
STRING_AGG
STRING_AGG(
[ DISTINCT ]
expression [, delimiter]
[ HAVING { MAX | MIN } expression2 ]
)
Description
Returns a value (either STRING
or BYTES
) obtained by concatenating
non-NULL
values. Returns NULL
if there are zero input rows or expression
evaluates to NULL
for all rows.
If a delimiter
is specified, concatenated values are separated by that
delimiter; otherwise, a comma is used as a delimiter.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
Either STRING
or BYTES
.
Return Data Types
Either STRING
or BYTES
.
Examples
SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;
+------------------------+
| string_agg |
+------------------------+
| apple,pear,banana,pear |
+------------------------+
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+------------------------------+
| string_agg |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+-----------------------+
| string_agg |
+-----------------------+
| apple & pear & banana |
+-----------------------+
SUM
SUM(
[ DISTINCT ]
expression
[ HAVING { MAX | MIN } expression2 ]
)
Description
Returns the sum of non-null values.
If the expression is a floating point value, the sum is non-deterministic, which means you might receive a different result each time you use this function.
To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
Supported Argument Types
Any supported numeric data types.
Return Data Types
INPUT | INT64 | NUMERIC | FLOAT64 |
---|---|---|---|
OUTPUT | INT64 | NUMERIC | FLOAT64 |
Special cases:
Returns NULL
if the input contains only NULL
s.
Returns NULL
if the input contains no rows.
Returns Inf
if the input contains Inf
.
Returns -Inf
if the input contains -Inf
.
Returns NaN
if the input contains a NaN
.
Returns NaN
if the input contains a combination of Inf
and -Inf
.
Examples
SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+-----+
| sum |
+-----+
| 25 |
+-----+
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;
+-----+
| sum |
+-----+
| 15 |
+-----+
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;
+------+
| sum |
+------+
| NULL |
+------+