Search code examples
mysqlsubquerysql-view

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

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: 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: Resulting Table

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.

Solution

  • 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;