Using WHERE inside aggregate functions

I've previously shared how aggregate functions in BigQuery SQL can pick their rows with HAVING MAX / HAVING MIN.

WHERE is now supported in preview (yes, I'm the tenth person posting it). It filters the input rows, so any aggregate can run on any subset of the data. The difference: HAVING MAX picks the extreme rows of the group; WHERE picks rows by conditions you know beforehand.

Sure, you could solve most of this before with SUM(CASE WHEN ... END), a classic SQL interview question. To me, a WHERE like this is more intuitive and pleasing to the eye. Very BigQuery!

SELECT
    -- 1. No condition: include every row.
    SUM(amount) AS total_sales,

    -- 2. One condition: each aggregate selects its own category.
    SUM(amount WHERE category = 'fruit') AS fruit_all_time_sales,
    SUM(amount WHERE category = 'vegetable') AS vegetable_all_time_sales,

    -- 3. Two conditions: restrict both category and year.
    SUM(amount WHERE category = 'fruit' AND year = 2025) AS fruit_sales_2025,
    SUM(amount WHERE category = 'fruit' AND year = 2026) AS fruit_sales_2026,

    -- 4. IN selects several countries; AND also restricts the category.
    SUM(amount WHERE category = 'fruit' AND country IN ('FR', 'NL')) AS fr_nl_fruit_all_time_sales,

    -- 5. The same matching rows can feed different aggregates.
    -- These four expressions all select the amounts 20 and 21.
    SUM(amount WHERE category = 'vegetable' AND country = 'NL') AS nl_vegetable_all_time_sales,
    MAX(amount WHERE category = 'vegetable' AND country = 'NL') AS highest_yearly_nl_vegetable_sales
FROM sales;