CODEWITHSUNDEEP
databasegoogle-bigqueryvisualizationdata-analysisdata-anomalies
BadassAnalyst
BadassAnalyst

Reputation: 1

Bigquery Sql not giving the expected result

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

Upvotes: 0

Views: 63

Answers (2)

Tonino
Tonino

Reputation: 1166

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

Upvotes: 1

Samuel
Samuel

Reputation: 3538

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

Upvotes: 1

Related Questions