SELECT list expression references column which is neither grouped nor aggregated at [position]

While using "group by" clause in BigQuery, you will have to add all the columns in the "select" clause to your "group by" clause as well (except the columns referenced in analytical functions like min(),max()) or it will throw the below error

SELECT list expression references column x which is neither grouped nor 
aggregated at [position]

Solution 💡

#1 Check if you are missing a GROUP BY statement

You might be using an analytical function in the SELECT clause without a GROUP BY clause

Example: 

SELECT location, 
       MAX(salary) AS highest_salary
FROM table

Try doing this instead:

SELECT location, 
       MAX(salary) AS highest_salary
FROM table
GROUP BY location;

#2 Check if you are missing any column in your group by statement

Make sure all the columns (except analytical functions eg. min()) are part of the group by clause

Example:

SELECT location, zipcode
       MAX(salary) AS highest_salary
FROM table
group by location;

Try doing this instead:

SELECT location, zipcode
       MAX(salary) AS highest_salary
FROM table
group by location, zipcode;

#3 You may not need a GROUP BY statement, try using Window functions instead

In some cases you only need to compute the MIN or MAX or SUM on only a subset of the columns and not all columns. In those cases, you should use a window function

Example:

SELECT location, zipcode
       MAX(salary) AS highest_salary
FROM table
group by location;

Try doing this instead:

SELECT location, zipcode
       MAX(salary) OVER (PARTITION BY location) AS highest_salary
FROM table

More Troubleshooting tips ⚡

• Check if the open and closing parenthesis are properly defined
• Check if the column case (upper or lowercase) is the same as defined in the table

victor

posted on 17 Mar 22