Google BigQuery API Syntax

The Google BigQuery API offers additional SQL operators and functions. A complete list of the available syntax is located at: https://cloud.google.com/bigquery/query-reference

Examples of Aggregate Functions

Below are several examples of SQL aggregate functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a reporting tool.

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

COUNT(DISTINCT)

Returns the number of distinct, non-null field values matching the query criteria.

SELECT COUNT(DISTINCT actor.attributes.email) AS DistinctValues FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

AVG

Returns the average of the column values.

SELECT repository.name, AVG(repository.watchers) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

MIN

Returns the minimum column value.

SELECT MIN(repository.watchers), repository.name FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

MAX

Returns the maximum column value.

SELECT repository.name, MAX(repository.watchers) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

SUM

Returns the total sum of the column values.

SELECT SUM(repository.watchers) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

COUNT(DISTINCT)

Returns the number of distinct, non-null field values matching the query criteria.

SELECT COUNT(DISTINCT actor.attributes.email) AS DistinctValues FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

AVG

Returns the average of the column values.

SELECT repository.name, AVG(repository.watchers) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'  GROUP BY repository.name

MIN

Returns the minimum column value.

SELECT MIN(repository.watchers), repository.name FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

MAX

Returns the maximum column value.

SELECT repository.name, MAX(repository.watchers) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

SUM

Returns the total sum of the column values.

SELECT SUM(repository.watchers) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

CORR

Returns the Pearson correlation coefficient of a set of number pairs.

SELECT repository.name, CORR(repository.watchers, repository.size) FROM [publicdata].[samples].github_nested

COVAR_POP

Computes the population covariance of the values computed by a set of number pairs.

SELECT repository.name, COVAR_POP(repository.watchers, repository.size) FROM [publicdata].[samples].github_nested

COVAR_SAMP

Computes the sample covariance of the values computed by a set of number pairs.

SELECT repository.name, COVAR_SAMP(repository.watchers, repository.size) FROM [publicdata].[samples].github_nested

NTH

Returns the nth sequential value in the scope of the function, where n is a constant. The NTH function starts counting at 1, so there is no zeroth term. If the scope of the function has less than n values, the function returns NULL.

SELECT repository.name, NTH(n, actor.attributes.email) FROM [publicdata].[samples].github_nested

STDDEV

Returns the standard deviation of the computed values. Rows with a NULL value are not included in the calculation.

SELECT repository.name, STDDEV(repository.watchers) FROM [publicdata].[samples].github_nested