BizTalk Adapter for Google BigQuery

Build 20.0.7654

Aggregate Functions

Google BigQuery API Syntax

The Google BigQuery API offers additional SQL operators and functions. A complete list of the available syntax is located at:

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.


Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM publicdata.samples.github_nested WHERE = 'EntityFramework'


Returns the average of the column values.

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


Returns the minimum column value.

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


Returns the maximum column value.

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


Returns the total sum of the column values.

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


Returns the Pearson correlation coefficient of a set of number pairs.
SELECT, CORR(repository.watchers, repository.size) FROM publicdata.samples.github_nested


Computes the population covariance of the values computed by a set of number pairs.
SELECT, COVAR_POP(repository.watchers, repository.size) FROM publicdata.samples.github_nested


Computes the sample covariance of the values computed by a set of number pairs.
SELECT, COVAR_SAMP(repository.watchers, repository.size) FROM publicdata.samples.github_nested


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, NTH(n, FROM publicdata.samples.github_nested


Returns the standard deviation of the computed values. Rows with a NULL value are not included in the calculation.
SELECT, STDDEV(repository.watchers) FROM publicdata.samples.github_nested

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7654