Resolved: Using HAVING without GROUP BY
I was wondering, what would be the SQL statement for the following: get all employees hired after 1 january 1999, whose names are counted less than 200 times but don't return grouped results.
Thus, each unique employee, independent of having a double name, should be returned as a single row - meaning names can not be grouped.
Hi Michiel,
Based on how WHERE and HAVING clauses works (as explained in the lecture), I think we can use HAVING without GROUP BY, but it will work just like WHERE after the SELECT statement. That is because SQL works in the order of FROM, WHERE, and SELECT. Only then will other clauses proceed, such as GROUP BY, HAVING, and ORDER BY.
But, I was able to pull the output you wanted by inserting the code used in the lecture as a subquery, shown below:
SELECT
*
FROM
employees
WHERE first_name IN (
SELECT
first_name
FROM
employees
WHERE hire_date > '1999-01-01'
GROUP BY first_name
HAVING COUNT(first_name) < 200
)
ORDER BY
first_name ASC,
last_name ASC;
and here's a subset of the output:
Hope this helps,
Carl
That's amazing, thanks so much for answering. Did not now you could have another query inside IN(), learned a lot from this answer.