Counts, totals and COUNTIF

Opening your lesson

Aggregates turn many rows into one: COUNT(*), SUM, AVG, MIN, MAX and COUNT(DISTINCT x). With GROUP BY you get one row per group, and every plain column in the SELECT list must be in the GROUP BY.

BigQuery lets you group by position or by alias: GROUP BY 1 or GROUP BY status. COUNTIF(condition) counts the rows where the condition is true, which saves a SUM(IF(...)). HAVING filters groups after they're counted, WHERE filters rows before.

For huge tables, APPROX_COUNT_DISTINCT(x) gives a close estimate much faster than COUNT(DISTINCT x).

SELECT channel, COUNT(*) AS orders, COUNTIF(status = 'refunded') AS refunded
FROM shop.orders
GROUP BY channel;

One row per channel: all orders and the refunded ones.

Choose your character

Your Work Gym account