Why am I getting a value error in my google sheets query?-CodePudding

I took an Udemy class in Google Sheets and I'm trying a formula on a similar dataset as I learned on, but I'm getting a value error. Please help.

The query is: =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1uJAwHzcg_MYBS08jqnWGcqe7oBIlPyVWCQN48G6tFfE/edit#gid=790495475","sephora_website_dataset2!$A:$G"),"SELECT Col2,Average(Col7) WHERE Col2 IS NOT NULL GROUP BY Col2 ORDER BY Average(Col7) desc LIMIT 10",-1)

Here is what the dataset looks like: https://i.stack.imgur.com/Qm6qx.png

CodePudding user response：

It doesn't know Average, use avg instead.

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1uJAwHzcg_MYBS08jqnWGcqe7oBIlPyVWCQN48G6tFfE/edit#gid=790495475","sephora_website_dataset2!$A:$G"),"SELECT Col2,avg(Col7) WHERE Col2 IS NOT NULL GROUP BY Col2 ORDER BY avg(Col7) desc LIMIT 10",-1)

CodePudding user response：

The name of the average-function in your query is avg, not "Average".

So the correct formula would be:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1uJAwHzcg_MYBS08jqnWGcqe7oBIlPyVWCQN48G6tFfE/edit#gid=790495475","sephora_website_dataset2!$A:$G"),"SELECT Col2, avg(Col7) WHERE Col2 IS NOT NULL GROUP BY Col2 ORDER BY avg(Col7) desc LIMIT 10", -1)

CodePudding user response：

use:

=QUERY(IMPORTRANGE("1uJAwHzcg_MYBS08jqnWGcqe7oBIlPyVWCQN48G6tFfE", "sephora_website_dataset2!A:G"),
 "select Col2,avg(Col7) 
  where Col2 is not null
  group by Col2 
  order by avg(Col7) desc 
  limit 10
  label avg(Col7)''", 0)