02 Feb 19 · victor · #Bigquery ·   Bookmark   ×

BigQuery error : SELECT list expression references column name which is neither grouped nor aggregated at [position]

Problem 🔍

When 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 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 02 Feb 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds