[SOLVED] In SQL, what do I add below to average the depths by year?

In SQL, what do I add below to average the depths by year?

The code before works just fine, but I can't figure out the next step at all. Next, I want all locations averaged by year and output a new column called annual_average.

My dataset is here: https://mgs-maine.opendata.arcgis.com/datasets/maine-snow-survey-data/explore?showTable=true.

SELECT  
  SITE_NAME,
  SURVEY_YEAR,
  AVG(DEPTH) AS snow_depth,
FROM `capstone-433319.maine_snow_survey.snow_data`
WHERE
  SURVEY_YEAR>=1984 AND
  DEPTH>=0 AND
  SITE_NAME IS NOT NULL
GROUP BY  
  SURVEY_YEAR,
  SITE_NAME
ORDER BY
  SITE_NAME

Solution

Try using window function to count average value by SITE_NAME

SELECT  
  "SITE_NAME" ,
  "SURVEY_YEAR" ,
  AVG("DEPTH") AS snow_depth,
  round(AVG("DEPTH") over (partition by "SITE_NAME"),2) as annual_average
FROM maine_snow_survey_data
WHERE
  "SURVEY_YEAR">=1984 AND
  "DEPTH">=0 AND
  "SITE_NAME" IS NOT NULL
GROUP BY  
  "SURVEY_YEAR",
  "SITE_NAME",
  "DEPTH"
ORDER BY
  "SITE_NAME";