Help using alias in SQL calculation

Google Cloud Data Analytics
1

I tried to practice calculation and aliasing as a SQL learner. I don’t understand why I get an error on the following. Can someone explain? Thank you!

Error is:

Unrecognized name: Y_2016 at [3:3]

SELECT

station_name,
Y_2016,
Y_2017,
Y_2018,

(Y_2016 + Y_2017 + Y_2018) / 3 AS AVG_2016_2018

(

SELECT

ridership_2016 AS Y_2016,
ridership_2017 AS Y_2017,
ridership_2018 AS Y_2018,

FROM

bigquery-public-data.new_york_subway.subway_ridership_2013_present

)

FROM

bigquery-public-data.new_york_subway.subway_ridership_2013_present

2

The error you’re encountering is due to the fact that you’re trying to use aliases defined in a subquery in the outer query, but the subquery itself is not properly defined or used.

In SQL, you can’t use aliases defined in a subquery in the outer query unless the subquery is defined in the FROM clause. The subquery should be given an alias and then you can use the aliases defined in the subquery in the outer query.

Here’s how you can correct your SQL query:

SELECT
station_name,
sub.Y_2016,
sub.Y_2017,
sub.Y_2018,
(sub.Y_2016 + sub.Y_2017 + sub.Y_2018) / 3 AS AVG_2016_2018
FROM
(
SELECT
station_name,
ridership_2016 AS Y_2016,
ridership_2017 AS Y_2017,
ridership_2018 AS Y_2018
FROM
bigquery-public-data.new_york_subway.subway_ridership_2013_present
) sub

3

Thank you! I understand the system of aliasing and subquery so much better now!