Simple query with HAVING operator

Question

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

Answer 1

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.

Answer 2

  SELECT Department, COUNT(Employee) AS cnt
    FROM Employees
GROUP BY Department
  HAVING COUNT(Employee) > 5

This works on SQL Server as well.

Answer 3

I have tried it.. the following query just works fine.. hope this helps...

SELECT Department FROM Employees GROUP BY Department HAVING count(Employee) > 2;