Grouping BigQuery Results for Average Calculations

What the GROUP BY clause is

In standard SQL and GoogleSQL for BigQuery, the GROUP BY clause groups rows that share identical values in specified summary columns into summary rows. When aggregate functions such as AVG(), COUNT(), or SUM() are used alongside non-aggregated columns or expressions in a SELECT list, all non-aggregated columns must be included in the GROUP BY clause.

How it meets the requirement

• Resolves aggregation error (✓): The original query selects subscriber_type and EXTRACT(HOUR FROM start_time) AS hour_of_day without aggregating them. Adding GROUP BY subscriber_type, hour_of_day informs BigQuery to group records by these unique attribute combinations.
• Enables AVG() calculation (✓): Once grouped, the AVG(duration_minutes) function calculates the mean trip duration for each distinct combination of subscriber type and hour.
• Supports BigQuery alias grouping (✓): BigQuery allows grouping directly by column aliases defined in the SELECT list (such as hour_of_day) as well as the original column references.

Key advantages and capabilities

• Multi-dimensional aggregation: Allows grouping across multiple dimensions (e.g., categories and extracted time units simultaneously).
• Optimized execution: BigQuery leverages distributed computation to parallelize aggregation across large datasets.
• Compatible with scalar date/time functions: Works seamlessly with date/time extraction functions like EXTRACT() and FORMAT_TIMESTAMP().

Why this is optimal

The GROUP BY clause is the standard, syntactically required mechanism in BigQuery SQL to perform grouping transformations before evaluating aggregate functions.