How can I query an SQL view that utilizes the YEAR() function in creation?-CodePudding

I have a task to query a view that I have just created from a table that utilizes the SQL YEAR() function in order to get the dates that a hypothetical employee was hired (all info in this DB is created for academic purposes and not legitimate information). I am able to create the view with no issues, but when trying to query the view after creation only gives me errors.

A snippet of the table the view was created from:

CREATE VIEW HiresByYear AS
SELECT YEAR(hire_date), COUNT(employee_id)
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY YEAR(hire_date) ASC;

This is the query that I am using to create the view from the table above, which results in a view that looks like this:

My question is how can I further query this view based on the YEAR(hire_date) column in the view? I've tried this query (which I know is not the correct way to query a view, but this is the ultimate goal I am trying to produce):

SELECT *
FROM HiresByYear
WHERE YEAR(hire_date) = 1997;

The above only results in an error as SQL can not locate the 'hire_date' column. Any tips?

Edit for clarity: I am required to use the YEAR() function in creating the view, my ultimate goal is to see how many employees were hired in 1997 specifically.

CodePudding user response：

You need to assign aliases to the columns that you select. These become the column names in the view.

CREATE VIEW HiresByYear AS
    SELECT YEAR(hire_date) AS year, COUNT(employee_id) AS count
    FROM employees
    GROUP BY year
    ORDER BY year ASC;

SELECT *
FROM HiresByYear
WHERE year = 1997;