I have a table of Employees which consists of two columns : Employee and DepartmentId as follows
|Employee | DepartmentId
-------------------------
| e1 | 1
| e2 | 1
| e3 | 1
| e4 | 2
| e5 | 2
| e6 | 3
| e7 | 3
| e8 | 3
| e9 | 4
| e10 | 5
| e11 | 6
I want to select departments that have more than two employees with simple query. Came up with following :
SELECT Department,
COUNT(Employee) as Quantity
FROM Employees
GROUP BY Department
HAVING (Quantity > 3)
ORDER BY Department
But during execution it complains about invalid column name (Quantity). I'm pretty sure that using aggregate function twice (select count() ... having count()) is not correct. Am i missing something?
p.s. "Straightforward" solution is i guess
SELECT Department
FROM (SELECT Department, COUNT(Employee) AS Quantity
FROM Employees
GROUP BY Department)
WHERE Quantity > 5
Writing
HAVING COUNT(*) > 3
is fine. The database will only evaluate the expression once and reuse the result in both the select and having clauses.Your example where you select the value in a subquery is also fine (apart from you need an alias for the subquery). Using this technique is not necessary here but it can be useful if the expression is more complicated and you want to avoid repeating the code for maintainability reasons.
Note that in MySQL the query you want to write would work. But this a MySQL specific extension and the same technique won't work in SQL Server.