Legacy SQL Functions and Operators

This document details legacy SQL functions and operators. The preferred query syntax for BigQuery is GoogleSQL. For information on GoogleSQL, see GoogleSQL Functions and Operators.

Supported functions and operators

Most SELECT statement clauses support functions. Fields referenced in a function don't need to be listed in any SELECT clause. Therefore, the following query is valid, even though the clicks field is not displayed directly:

#legacySQL
SELECT country, SUM(clicks) FROM table GROUP BY country;
Aggregate functions
AVG() Returns the average of the values for a group of rows ...
BIT_AND() Returns the result of a bitwise AND operation ...
BIT_OR() Returns the result of a bitwise OR operation ...
BIT_XOR() Returns the result of a bitwise XOR operation ...
CORR() Returns the Pearson correlation coefficient of a set of number pairs.
COUNT() Returns the total number of values ...
COUNT([DISTINCT]) Returns the total number of non-NULL values ...
COVAR_POP() Computes the population covariance of the values ...
COVAR_SAMP() Computes the sample covariance of the values ...
EXACT_COUNT_DISTINCT() Returns the exact number of non-NULL, distinct values for the specified field.
FIRST() Returns the first sequential value in the scope of the function.
GROUP_CONCAT() Concatenates multiple strings into a single string ...
GROUP_CONCAT_UNQUOTED() Concatenates multiple strings into a single string ... will not add double quotes ...
LAST() Returns the last sequential value ...
MAX() Returns the maximum value ...
MIN() Returns the minimum value ...
NEST() Aggregates all values in the current aggregation scope into a repeated field.
NTH() Returns the nth sequential value ...
QUANTILES() Computes approximate minimum, maximum, and quantiles ...
STDDEV() Returns the standard deviation ...
STDDEV_POP() Computes the population standard deviation ...
STDDEV_SAMP() Computes the sample standard deviation ...
SUM() Returns the sum total of the values ...
TOP() ... COUNT(*) Returns the top max_records records by frequency.
UNIQUE() Returns the set of unique, non-NULL values ...
VARIANCE() Computes the variance of the values ...
VAR_POP() Computes the population variance of the values ...
VAR_SAMP() Computes the sample variance of the values ...
Arithmetic operators
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulo
Bitwise functions
& Bitwise AND
| Bitwise OR