Resolved: Using HAVING without GROUP BY

Question

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.

Answer 1

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

Answer 2

That's amazing, thanks so much for answering. Did not now you could have another query inside IN(), learned a lot from this answer.

Resolved: Using HAVING without GROUP BY

Submit an answer

related questions