# 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.

Source: https://docs.querylab.io/bigquery/aggregate-functions/

Updated: 2026-10-06

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:

| customer | order_id | amount | paid  | day        |
| -------- | -------- | ------ | ----- | ---------- |
| ana      | o1       | 30     | true  | 2026-03-01 |
| ana      | o2       | 75     | false | 2026-03-04 |
| ben      | o3       | 20     | true  | 2026-03-02 |
| ben      | o4       | 45     | true  | 2026-03-05 |
| cai      | o5       | 60     | false | 2026-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**

```sql
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 (3 rows):

| customer | orders | paid_orders | all_paid | any_large |
| --- | --- | --- | --- | --- |
| ana | 2 | 1 | false | true |
| ben | 2 | 2 | true | false |
| cai | 1 | 0 | false | true |

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

**SUM(...) FILTER (WHERE ...)**

```sql
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
```

BigQuery rejects this query:

```text
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**

```sql
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 (1 row):

| paid_total | same_thing |
| --- | --- |
| 95 | 95 |

## A value from the max or min row

"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**

```sql
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 (3 rows):

| customer | latest_order | first_amount | has_any |
| --- | --- | --- | --- |
| ana | o2 | 30 | true |
| ben | o4 | 20 | true |
| cai | o5 | 60 | true |

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

**ANY_VALUE ... HAVING MAX**

```sql
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 (3 rows):

| customer | latest_order |
| --- | --- |
| ana | o2 |
| ben | o4 |
| cai | o5 |

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](https://docs.querylab.io/bigquery/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**

```sql
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 (3 rows):

| customer | orders_in_order | biggest |
| --- | --- | --- |
| ana | o1, o2 | 75 |
| ben | o3, o4 | 45 |
| cai | o5 | 60 |

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](https://docs.querylab.io/bigquery/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**

```sql
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 (1 row):

| customers | distinct_amount_sum | avg_amount | approx_customers |
| --- | --- | --- | --- |
| 3 | 230 | 46.0 | 3 |

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](https://docs.querylab.io/bigquery/approximate-aggregation/).

## WHERE, HAVING and aliases

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

**SUM in WHERE**

```sql
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
```

BigQuery rejects this query:

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

Querylab.io flags an aggregate in `WHERE` as an error and offers a Change WHERE to HAVING quick fix that moves the condition ([Code quality](https://docs.querylab.io/writing-sql/code-quality/)).

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

**HAVING total > 70**

```sql
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 (1 row):

| customer | total |
| --- | --- |
| ana | 105 |

## 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**

```sql
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 (1 row):

| total | row_count | biggest |
| --- | --- | --- |
| NULL | 0 | NULL |

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](https://docs.querylab.io/bigquery/dates-and-times/#filling-in-missing-days).

## Other aggregates worth knowing

- `COUNT(*)` counts rows, `COUNT(x)` counts non-NULL values of `x`. See [NULL](https://docs.querylab.io/bigquery/null-handling/#aggregates-skip-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](https://docs.querylab.io/bigquery/window-functions/).

Try COUNTIF and LOGICAL_AND on a few orders. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Aggregate functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions)

- [Aggregate function calls](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls)