Docs
Playground Open app
Docs /Querying /Aggregate functions
Querying

Aggregate functions in BigQuery

BigQuery's aggregates beyond SUM and COUNT, with the details that differ from other databases. COUNTIF, LOGICAL_AND, MAX_BY, ANY_VALUE ... HAVING MAX, ordered STRING_AGG and ARRAY_AGG ... LIMIT, no FILTER clause, and what aggregates return for zero rows.

BigQuery has the standard aggregates, COUNT, SUM, AVG, MIN and MAX, plus several that save a subquery or a CASE in queries other databases make you write the long way. This page covers the ones that are specific to BigQuery and the rules that differ. All examples use five orders:

customerorder_idamountpaidday
anao130true2026-03-01
anao275false2026-03-04
beno320true2026-03-02
beno445true2026-03-05
caio560false2026-03-03

Counting and testing conditions

COUNTIF(condition) counts the rows where the condition is true. LOGICAL_AND is true when the condition holds for every row, and LOGICAL_OR when it holds for at least one:

COUNTIF, LOGICAL_AND and LOGICAL_OR per customer
12345678910111213141516
WITH orders AS (
  SELECT 'ana' AS customer, 'o1' AS order_id, 30 AS amount, TRUE AS paid, DATE '2026-03-01' AS day UNION ALL
  SELECT 'ana', 'o2', 75, FALSE, DATE '2026-03-04' UNION ALL
  SELECT 'ben', 'o3', 20, TRUE, DATE '2026-03-02' UNION ALL
  SELECT 'ben', 'o4', 45, TRUE, DATE '2026-03-05' UNION ALL
  SELECT 'cai', 'o5', 60, FALSE, DATE '2026-03-03'
)
SELECT
  customer,
  COUNT(*) AS orders,
  COUNTIF(paid) AS paid_orders,
  LOGICAL_AND(paid) AS all_paid,
  LOGICAL_OR(amount > 50) AS any_large
FROM orders
GROUP BY customer
ORDER BY customer
Result in BigQuery
customerorderspaid_ordersall_paidany_large
1ana21falsetrue
2ben22truefalse
3cai10falsetrue
3 rows

BigQuery has no FILTER (WHERE ...) clause on aggregates, which Postgres has:

SUM(...) FILTER (WHERE ...)BigQuery rejects this query
12345678910
WITH orders AS (
  SELECT 'ana' AS customer, 'o1' AS order_id, 30 AS amount, TRUE AS paid, DATE '2026-03-01' AS day UNION ALL
  SELECT 'ana', 'o2', 75, FALSE, DATE '2026-03-04' UNION ALL
  SELECT 'ben', 'o3', 20, TRUE, DATE '2026-03-02' UNION ALL
  SELECT 'ben', 'o4', 45, TRUE, DATE '2026-03-05' UNION ALL
  SELECT 'cai', 'o5', 60, FALSE, DATE '2026-03-03'
)
SELECT
  SUM(amount) FILTER (WHERE paid) AS paid_total
FROM orders
Error in BigQuery

Syntax error: Expected end of input but got "(" at [9:22]

Use COUNTIF, or put the condition inside the aggregate with IF. Both forms below give 95. SUM(IF(paid, amount, NULL)) would too, because the aggregate skips NULLs, but it returns NULL instead of 0 when no row matches:

a conditional SUM
1234567891011
WITH orders AS (
  SELECT 'ana' AS customer, 'o1' AS order_id, 30 AS amount, TRUE AS paid, DATE '2026-03-01' AS day UNION ALL
  SELECT 'ana', 'o2', 75, FALSE, DATE '2026-03-04' UNION ALL
  SELECT 'ben', 'o3', 20, TRUE, DATE '2026-03-02' UNION ALL
  SELECT 'ben', 'o4', 45, TRUE, DATE '2026-03-05' UNION ALL
  SELECT 'cai', 'o5', 60, FALSE, DATE '2026-03-03'
)
SELECT
  SUM(IF(paid, amount, 0)) AS paid_total,
  SUM(amount * IF(paid, 1, 0)) AS same_thing
FROM orders
Result in BigQuery
paid_totalsame_thing
19595
1 row

A value from the row with the largest or smallest something

“The latest order per customer” is a common question. MAX_BY(x, y) returns x from the row where y is largest, and MIN_BY from the row where it is smallest:

MAX_BY and MIN_BY
123456789101112131415
WITH orders AS (
  SELECT 'ana' AS customer, 'o1' AS order_id, 30 AS amount, TRUE AS paid, DATE '2026-03-01' AS day UNION ALL
  SELECT 'ana', 'o2', 75, FALSE, DATE '2026-03-04' UNION ALL
  SELECT 'ben', 'o3', 20, TRUE, DATE '2026-03-02' UNION ALL
  SELECT 'ben', 'o4', 45, TRUE, DATE '2026-03-05' UNION ALL
  SELECT 'cai', 'o5', 60, FALSE, DATE '2026-03-03'
)
SELECT
  customer,
  MAX_BY(order_id, day) AS latest_order,
  MIN_BY(amount, day) AS first_amount,
  ANY_VALUE(order_id) IS NOT NULL AS has_any
FROM orders
GROUP BY customer
ORDER BY customer
Result in BigQuery
customerlatest_orderfirst_amounthas_any
1anao230true
2beno420true
3caio560true
3 rows

ANY_VALUE(x HAVING MAX y) is the older spelling of the same thing, and HAVING MIN the opposite:

ANY_VALUE ... HAVING MAX
12345678910111213
WITH orders AS (
  SELECT 'ana' AS customer, 'o1' AS order_id, 30 AS amount, TRUE AS paid, DATE '2026-03-01' AS day UNION ALL
  SELECT 'ana', 'o2', 75, FALSE, DATE '2026-03-04' UNION ALL
  SELECT 'ben', 'o3', 20, TRUE, DATE '2026-03-02' UNION ALL
  SELECT 'ben', 'o4', 45, TRUE, DATE '2026-03-05' UNION ALL
  SELECT 'cai', 'o5', 60, FALSE, DATE '2026-03-03'
)
SELECT
  customer,
  ANY_VALUE(order_id HAVING MAX day) AS latest_order
FROM orders
GROUP BY customer
ORDER BY customer
Result in BigQuery
customerlatest_order
1anao2
2beno4
3caio5
3 rows

Plain ANY_VALUE(x) returns x from some row of the group, without saying which. It is the right choice for a column that is the same on every row of the group, like a customer’s name when you group by customer ID.

When you need the whole row rather than one column, use QUALIFY with ROW_NUMBER().

Strings and arrays from a group

STRING_AGG joins strings with a separator, and ARRAY_AGG collects values into an array. Both take an ORDER BY, and ARRAY_AGG also a LIMIT:

orders in date order, and the largest amount
1234567891011121314
WITH orders AS (
  SELECT 'ana' AS customer, 'o1' AS order_id, 30 AS amount, TRUE AS paid, DATE '2026-03-01' AS day UNION ALL
  SELECT 'ana', 'o2', 75, FALSE, DATE '2026-03-04' UNION ALL
  SELECT 'ben', 'o3', 20, TRUE, DATE '2026-03-02' UNION ALL
  SELECT 'ben', 'o4', 45, TRUE, DATE '2026-03-05' UNION ALL
  SELECT 'cai', 'o5', 60, FALSE, DATE '2026-03-03'
)
SELECT
  customer,
  STRING_AGG(order_id, ', ' ORDER BY day) AS orders_in_order,
  ARRAY_AGG(amount ORDER BY amount DESC LIMIT 1)[OFFSET(0)] AS biggest
FROM orders
GROUP BY customer
ORDER BY customer
Result in BigQuery
customerorders_in_orderbiggest
1anao1, o275
2beno3, o445
3caio560
3 rows

Without ORDER BY inside the call, the order of the elements isn’t defined and can change between runs. ARRAY_AGG(x ORDER BY y DESC LIMIT 1)[OFFSET(0)] is another way to write “the value from the top row”, and with a larger LIMIT it gives the top N per group. An array with a NULL element can’t be part of a query result, so ARRAY_AGG over a column with NULLs fails unless you add IGNORE NULLS (or only use the array inside the query, for example with ARRAY_LENGTH). See Arrays.

DISTINCT

COUNT(DISTINCT x), SUM(DISTINCT x) and STRING_AGG(DISTINCT x) work on distinct values. APPROX_COUNT_DISTINCT gives an estimate that uses far less memory on large data:

exact and approximate distinct counts
12345678910111213
WITH orders AS (
  SELECT 'ana' AS customer, 'o1' AS order_id, 30 AS amount, TRUE AS paid, DATE '2026-03-01' AS day UNION ALL
  SELECT 'ana', 'o2', 75, FALSE, DATE '2026-03-04' UNION ALL
  SELECT 'ben', 'o3', 20, TRUE, DATE '2026-03-02' UNION ALL
  SELECT 'ben', 'o4', 45, TRUE, DATE '2026-03-05' UNION ALL
  SELECT 'cai', 'o5', 60, FALSE, DATE '2026-03-03'
)
SELECT
  COUNT(DISTINCT customer) AS customers,
  SUM(DISTINCT amount) AS distinct_amount_sum,
  AVG(amount) AS avg_amount,
  APPROX_COUNT_DISTINCT(customer) AS approx_customers
FROM orders
Result in BigQuery
customersdistinct_amount_sumavg_amountapprox_customers
1323046.03
1 row

On small data the two counts agree. On large data the approximate version is faster and uses less memory; on a million rows our test was off by 0.6%. See Approximate aggregation.

WHERE, HAVING and aliases

Aggregates can’t appear in WHERE, which runs before grouping:

SUM in WHEREBigQuery rejects this query
1234567891011
WITH orders AS (
  SELECT 'ana' AS customer, 'o1' AS order_id, 30 AS amount, TRUE AS paid, DATE '2026-03-01' AS day UNION ALL
  SELECT 'ana', 'o2', 75, FALSE, DATE '2026-03-04' UNION ALL
  SELECT 'ben', 'o3', 20, TRUE, DATE '2026-03-02' UNION ALL
  SELECT 'ben', 'o4', 45, TRUE, DATE '2026-03-05' UNION ALL
  SELECT 'cai', 'o5', 60, FALSE, DATE '2026-03-03'
)
SELECT customer, SUM(amount) AS total
FROM orders
WHERE SUM(amount) > 50
GROUP BY customer
Error in BigQuery

Aggregate function SUM not allowed in WHERE clause at [10:7]

Filter on aggregates in HAVING. BigQuery lets HAVING use a SELECT alias, which many databases don’t:

HAVING total > 70
123456789101112
WITH orders AS (
  SELECT 'ana' AS customer, 'o1' AS order_id, 30 AS amount, TRUE AS paid, DATE '2026-03-01' AS day UNION ALL
  SELECT 'ana', 'o2', 75, FALSE, DATE '2026-03-04' UNION ALL
  SELECT 'ben', 'o3', 20, TRUE, DATE '2026-03-02' UNION ALL
  SELECT 'ben', 'o4', 45, TRUE, DATE '2026-03-05' UNION ALL
  SELECT 'cai', 'o5', 60, FALSE, DATE '2026-03-03'
)
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
HAVING total > 70
ORDER BY customer
Result in BigQuery
customertotal
1ana105
1 row

Aggregates over zero rows

With no GROUP BY, an aggregate over no rows still returns one row. COUNT returns 0, while SUM, AVG, MIN and MAX return NULL:

totals for a customer with no orders
12345678910111213
WITH orders AS (
  SELECT 'ana' AS customer, 'o1' AS order_id, 30 AS amount, TRUE AS paid, DATE '2026-03-01' AS day UNION ALL
  SELECT 'ana', 'o2', 75, FALSE, DATE '2026-03-04' UNION ALL
  SELECT 'ben', 'o3', 20, TRUE, DATE '2026-03-02' UNION ALL
  SELECT 'ben', 'o4', 45, TRUE, DATE '2026-03-05' UNION ALL
  SELECT 'cai', 'o5', 60, FALSE, DATE '2026-03-03'
)
SELECT
  SUM(amount) AS total,
  COUNT(*) AS row_count,
  MAX(amount) AS biggest
FROM orders
WHERE customer = 'nobody'
Result in BigQuery
totalrow_countbiggest
1null0null
1 row

A dashboard that shows SUM(amount) displays an empty cell, not 0, for a day with no sales. Wrap it in IFNULL(SUM(amount), 0) or COALESCE when 0 is the right answer. With a GROUP BY, groups with no rows don’t appear at all; to show them, join to a list of all groups, as in the calendar example in Dates and times.

Other aggregates worth knowing

  • COUNT(*) counts rows, COUNT(x) counts non-NULL values of x. See NULL.
  • BIT_AND, BIT_OR, BIT_XOR combine integer flags.
  • CORR, COVAR_POP, STDDEV, VARIANCE and their _SAMP versions for statistics.
  • APPROX_QUANTILES and APPROX_TOP_COUNT for medians, percentiles and top values on large data.
  • ARRAY_CONCAT_AGG concatenates arrays from each row.
  • Most aggregates can also be window functions with OVER (...). See Window functions.