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
