Why can't I do this code instead?

Question

SELECT
    first_name, COUNT(first_name) AS names_count
FROM
    employees
GROUP BY first_name
HAVING COUNT(first_name) < 200
    AND hire_date > '1999-01-01'
ORDER BY first_name DESC;

Answer 1

Hi Marcus!

Thanks for reaching out.

By executing the query you suggest, don't you obtain an error message saying that the 'hire_date' in the HAVING clause is unknown? If not, what output do you obtain on execution of the given query? Thank you.

Looking forward to your answer.
Best,
Martin

Answer 2

Bhushan Mhadgut

Posted on:

28 Oct 2021

0

Yes, we get this error.

Answer 3

Hi Bhushan!

Thanks for reaching out!
Thank you for your answer and please excuse me for the late reply.

The error message is most probably obtained because the hire_date we are referring to has not been mentioned in the field list. Please retry with a query beginning by:
SELECT
first_name, COUNT(first_name) AS names_count, hire_date
FROM
...
However, you might obtain an empty output. This is because in the condition you are looking for a number of records per hire date to be smaller than 200. To obtain an output containing records from the table, you need to change the condition to, e.g., > 200.

Hope this helps.
Best,
Martin

Why can't I do this code instead?

Submit an answer

related questions