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"

Window Functions

Ranking and analytics

Numeric Functions

Math and rounding

All Functions

Browse all function categories

SELECT

GROUP BY and HAVING clauses

Overview

Data Types

SQL Commands

Operators

Functions

General Reference

Aggregate Functions