Skip to main content

​
Aggregate functions

Aggregate functions compute a single result from a set of rows.

​
COUNT

Counts rows or non-null values. Use COUNT(1) for total row counts and COUNT(column) for counting non-null values. 
SELECT COUNT(1) AS total_rows FROM company_data."123"
SELECT COUNT(email) AS emails_present FROM company_data."123"

-- For distinct counts, prefer APPROX_COUNT_DISTINCT (see below)
SELECT COUNT(DISTINCT user_id) AS unique_users FROM company_data."123"

​
APPROX_COUNT_DISTINCT

Returns the approximate number of distinct values in a column. Faster and cheaper than COUNT(DISTINCT ...), and returns exact results for low-cardinality columns. Use this instead of COUNT(DISTINCT ...) unless your query requires a guaranteed exact count. 
SELECT APPROX_COUNT_DISTINCT(user_id) AS unique_users FROM company_data."123"
APPROX_COUNT_DISTINCT is the recommended way to count unique values in most queries. It produces exact results when the number of distinct values is small and near-exact results at scale—while using significantly fewer resources than COUNT(DISTINCT ...).

​
SUM

Returns the sum of values. 
SELECT SUM(amount) AS total_amount FROM company_data."123"

​
AVG

Returns the average of values. 
SELECT AVG(score) AS average_score FROM company_data."123"

​
MIN / MAX

Returns the minimum or maximum value. 
SELECT MIN(created_at) AS earliest FROM company_data."123"
SELECT MAX(score) AS highest_score FROM company_data."123"

​
STDDEV_POP / STDDEV_SAMP

Returns population or sample standard deviation. 
SELECT STDDEV_POP(value) AS std_dev FROM company_data."123"
SELECT STDDEV_SAMP(value) AS sample_std_dev FROM company_data."123"

​
VAR_POP / VAR_SAMP

Returns population or sample variance. 
SELECT VAR_POP(value) AS variance FROM company_data."123"
SELECT VAR_SAMP(value) AS sample_variance FROM company_data."123"

​
ARRAY_AGG

Aggregates values into an array. 
SELECT user_id, ARRAY_AGG(tag) AS all_tags
FROM company_data."123"
GROUP BY user_id

​
STRING_AGG

Concatenates values into a delimited string. 
SELECT user_id, STRING_AGG(category, ', ') AS categories
FROM company_data."123"
GROUP BY user_id

​
PERCENTILE_CONT

Returns a percentile value using continuous distribution. 
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS median
FROM company_data."123"

SELECT PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time) AS p95
FROM company_data."123"

​
Related content

Window Functions

Ranking and analytics

Numeric Functions

Math and rounding

All Functions

Browse all function categories

SELECT

GROUP BY and HAVING clauses