SQL : unrecognized name

Question

Unrecognized name: year at [6:7]

SELECT
    EXTRACT(YEAR FROM starttime) AS year,
    COUNT (*) AS number_of_rides
FROM
  bigquery-public-data.new_york.citibike_trips 
WHERE year IN
  (2013,2014)
GROUP BY
  year
ORDER BY
  year DESC

--wanted to return data in 2013 and 2014

Answer 1

You cannot use aliases defined in your select clause within your where-, group- and order-clauses in Google Bigquery. You need to spell out the function again. Replace each year in your clauses by EXTRACT(YEAR FROM starttime) and it should work.