Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 37 ยท Querying and Functions

Aggregates and GROUP BY in BigQuery

Learn BigQuery aggregation: COUNT, SUM, AVG, APPROX_COUNT_DISTINCT, ANY_VALUE, STRING_AGG, ARRAY_AGG, GROUP BY ROLLUP, HAVING, and aggregate filters, with runnable stand-ins.

Aggregation is what a warehouse is for: turning millions of rows into a few numbers. The basics (COUNT, SUM, GROUP BY) are the same as in any SQL database. BigQuery adds approximate aggregates that scale to billions of rows, array aggregates, and some syntax that makes reports shorter. This lesson goes from the basics to the BigQuery extras.

The standard aggregates

SQL Stand-in: the standard aggregates ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Remember how NULL behaves: COUNT(*) counts rows, COUNT(col) counts rows where col is not NULL, and SUM and AVG ignore NULLs. An empty input gives NULL for SUM but 0 for COUNT.

GROUP BY and HAVING

GROUP BY collapses rows into one per group, and HAVING filters groups after aggregating, while WHERE filters rows before:

SQL Stand-in: group and filter groups ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In BigQuery you can group by the alias or by position, or let GROUP BY ALL pick the non-aggregated columns for you:

SQL
SELECT
  country,
  DATE_TRUNC(signed_up, MONTH) AS signup_month,
  COUNT(*) AS customers
FROM `my-project.shop.customers`
GROUP BY ALL
ORDER BY signup_month, country;

GROUP BY ALL is convenient but can hide mistakes if you add a column by accident. In pipelines that matter, list the columns.

Approximate aggregates

Exact distinct counts on a billion rows are costly because every distinct value must travel between workers. BigQuery offers sketches:

SQL
SELECT
  APPROX_COUNT_DISTINCT(user_id)  AS approx_users,
  APPROX_QUANTILES(latency_ms, 100)[OFFSET(95)] AS p95_latency,
  APPROX_TOP_COUNT(page, 5)       AS top_pages
FROM `my-project.app.requests`;
  • APPROX_COUNT_DISTINCT is typically within about one percent and much cheaper.
  • APPROX_QUANTILES(x, n) returns an array of n+1 boundary values; index OFFSET(95) is the 95th percentile when n is 100.
  • APPROX_TOP_COUNT and APPROX_TOP_SUM return the most frequent values.

Use exact counts for finance and compliance; use approximate ones for dashboards and exploration.

Counting conditionally

Use a FILTER-style pattern. SQLite supports the SQL standard FILTER (WHERE ...) clause, but in BigQuery you use COUNTIF, SUM(IF(...)) or COUNT(IF(cond, x, NULL)):

SQL Stand-in: one pass, many conditional totals ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

(SUM(boolean) works in SQLite because comparisons return 0 or 1. In BigQuery use COUNTIF(status = 'delivered').)

STRING_AGG and ARRAY_AGG

Aggregates are not limited to numbers. STRING_AGG glues strings, and ARRAY_AGG collects values into an array, which is a native type in BigQuery:

SQL
SELECT
  customer_id,
  STRING_AGG(status, ', ' ORDER BY ordered_at) AS status_history,
  ARRAY_AGG(id ORDER BY ordered_at DESC LIMIT 3) AS last_three_orders
FROM `my-project.shop.orders`
GROUP BY customer_id;

You can order inside the aggregate and even LIMIT it. SQLite's version of string aggregation is group_concat:

SQL Stand-in: glue values per group ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

ANY_VALUE(col) picks an arbitrary value from the group. It is useful when the column is functionally dependent on the group key and you do not want to add it to GROUP BY.

ROLLUP, CUBE and GROUPING SETS

Reports often need subtotals. ROLLUP adds them in one query:

SQL
SELECT
  country,
  city,
  COUNT(*) AS customers
FROM `my-project.shop.customers`
GROUP BY ROLLUP (country, city)
ORDER BY country, city;

The output has a row per city, a row per country with city = NULL (the subtotal), and a grand total with both NULL. CUBE produces all combinations, and GROUPING SETS lets you list exactly which groupings you want. Use GROUPING(country) to tell a real NULL from a subtotal NULL.

SQLite has no ROLLUP, but a UNION ALL produces the same subtotal idea and shows what BigQuery is doing for you:

SQL Stand-in: subtotals by hand ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Aggregation and cost

Aggregation itself is cheap compared with reading. What you pay for is the columns scanned. A query that groups by country and sums total reads two columns, whatever the row count. Keep these in mind:

  • Filter early (WHERE on a partition column) so fewer rows are aggregated.
  • Prefer one pass with several conditional aggregates over several separate queries.
  • Pre-aggregate big tables into summary tables or materialized views when many people ask the same question.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between WHERE and HAVING?

    Rows are filtered by WHERE first, then grouped, then groups are filtered by HAVING.
  2. 2. When is APPROX_COUNT_DISTINCT a good choice?

    It trades about one percent accuracy for large savings in time and shuffle.
  3. 3. What does GROUP BY ROLLUP (country, city) add?

    ROLLUP adds higher-level subtotals, with NULLs in the rolled-up columns.
  4. 4. Which BigQuery function counts rows meeting a condition?

    COUNTIF(condition) is a shortcut for counting true conditions.

Summary

  • Standard aggregates behave as in SQL; know how NULL is treated.
  • HAVING filters groups; WHERE filters rows.
  • APPROX_* functions scale to huge data; use exact counts when accuracy matters.
  • ARRAY_AGG and STRING_AGG collect values; ROLLUP adds subtotals.
  • Cost depends on columns scanned, not on how much you aggregate.

Next: Dates, Times and Time Zones