Understanding BigQuery GROUP BY Clause Errors: Non-Aggregated Column References in SELECT Lists

Dec 05, 2025 · Programming · 9 views · 7.8

Keywords: BigQuery | GROUP BY | Aggregation Functions | Query Error | Data Grouping

Abstract: This article delves into the common BigQuery error "SELECT list expression references column which is neither grouped nor aggregated," using a specific case study to explain the workings of the GROUP BY clause and its restrictions on SELECT lists. It begins by analyzing the cause of the error, which occurs when using GROUP BY, requiring all expressions in the SELECT list to be either in the GROUP BY clause or use aggregation functions. Then, by refactoring the example code, it demonstrates how to fix the error by adding missing columns to the GROUP BY clause or applying aggregation functions. Additionally, the article discusses potential issues with the query logic and provides optimization tips to ensure semantic correctness and performance. Finally, it summarizes best practices to avoid such errors, helping readers better understand and apply BigQuery's aggregation query capabilities.

Error Cause Analysis

In BigQuery, when using the GROUP BY clause for grouped queries, expressions in the SELECT list must adhere to strict rules. Specifically, each expression must either appear in the GROUP BY clause or use an aggregation function (e.g., MIN, MAX, SUM). This rule ensures deterministic and consistent query results, avoiding ambiguity due to grouping. In the user's example, the error message "BIGQUERY SELECT list expression references column CHANNEL_ID which is neither grouped nor aggregated at [10:13]" clearly indicates that the column CHANNEL_ID is referenced in the SELECT list but is neither included in the GROUP BY clause nor uses any aggregation function. This violates BigQuery's query syntax rules, causing the system to throw an error.

Code Example and Fix

To illustrate the issue more clearly, we first refactor the user's original code. The original query consists of two subqueries selecting data from different tables and grouping by DATE. However, the SELECT lists of both subqueries include columns (CHANNEL_ID and CHANNEL_DISPLAY_NAME) not specified in the GROUP BY clause. Below is the fixed code example, resolving the issue by adding the missing columns to the GROUP BY clause:

SELECT 
    t0.CHANNEL_ID,
    t1.CHANNEL_DISPLAY_NAME
FROM
    (SELECT
         TIMESTAMP_TRUNC(_PARTITIONTIME, MONTH) as `DATE`,
         CHANNEL_ID
     FROM 
         `pops-204909.yt_kids.p_content_owner_basic_a3_yt_kids`   
     WHERE 
         DATE(_PARTITIONTIME) between "2017-08-01" and "2017-08-31"
     GROUP BY 
         `DATE`, CHANNEL_ID) t0
LEFT JOIN
    (SELECT
         TIMESTAMP_TRUNC(_PARTITIONTIME, MONTH) as `DATE`,
         CHANNEL_DISPLAY_NAME
     FROM 
         `pops-204909.yt_kids.p_content_owner_ad_revenue_raw_a1_yt_kids`
     WHERE 
         DATE(_PARTITIONTIME) between "2017-08-01" and "2017-08-31"
     GROUP BY 
         `DATE`, CHANNEL_DISPLAY_NAME) t1 ON t0.DATE = t1.DATE

In this fixed version, we add CHANNEL_ID and CHANNEL_DISPLAY_NAME to the GROUP BY clauses of their respective subqueries. This ensures that these columns in the SELECT list comply with the rules, as they are now included in the grouping conditions. An alternative fix is to use aggregation functions; for example, if business logic allows, one could apply MIN or MAX to CHANNEL_ID, but in this specific case, since the columns are identifiers, it is generally more appropriate to include them in GROUP BY.

In-Depth Discussion of Query Logic

Although the above fix addresses the syntax error, the original query's logic may have underlying issues. The user attempts to join two subqueries by grouping on DATE, but each subquery includes other columns (CHANNEL_ID and CHANNEL_DISPLAY_NAME). If these columns have multiple values for the same DATE, the grouping operation could lead to data loss or ambiguity. For instance, if the same DATE corresponds to multiple different CHANNEL_ID values, grouping might retain only one value (depending on the database implementation), which may not align with the user's intent. Therefore, when designing such queries, it is crucial to consider business requirements carefully, ensuring that the grouping logic aligns with the data model. If the goal is to retrieve combinations of each DATE and CHANNEL_ID, both should be included in the GROUP BY clause, as shown in the fixed code.

Best Practices and Conclusion

To avoid similar errors, it is recommended to follow these best practices when writing BigQuery queries: First, clarify the query's objective and determine if GROUP BY is necessary for grouping. If grouping is required, ensure all expressions in the SELECT list are either grouping columns or use aggregation functions. Second, in complex queries, use subqueries or Common Table Expressions (CTEs) to break down the logic, enhancing code readability and maintainability. Finally, always test query results to verify they meet business logic, especially when handling large datasets. By understanding the workings and limitations of GROUP BY, developers can leverage BigQuery more effectively for data analysis and aggregation, improving query accuracy and performance.

Copyright Notice: All rights in this article are reserved by the operators of DevGex. Reasonable sharing and citation are welcome; any reproduction, excerpting, or re-publication without prior permission is prohibited.