Bigquery Sql not giving the expected result-CodePudding

it keeps bringing this statement 'SELECT list expression references column stn which is neither grouped nor aggregated'

This is the query

SELECT stn, date, AVG(TEMP), FROM 'bigquery-public-data.noaa_gsod.gsod2020' WHERE stn="725030" OR stn="744860" AND date BETWEEN'2020-7-1'AND '2020-7-30'

Tried to run a query and was expecting a result but could not get it

CodePudding user response：

an avg function requires a group by.

You should group by the first 2 columns in order to find an avg for the same stn/date example of group query

SELECT stn, date, AVG(TEMP) 
FROM 'bigquery-public-data.noaa_gsod.gsod2020' 
WHERE stn="725030" OR stn="744860" AND date BETWEEN'2020-7-1'AND '2020-7-30'
group by 1,2

CodePudding user response：

You need to group the data. As soon as an aggregation function, such as AVG is used, the other columns need to be listed after the group by.

SELECT
  stn,
  date,
  AVG(TEMP),
FROM
  `bigquery-public-data.noaa_gsod.gsod2020`
WHERE
  stn="725030"
  OR stn="744860"
  AND date BETWEEN'2020-7-1'AND '2020-7-30'
GROUP BY 1,2   # 1st and 2nd column are dimension for the AVG-function