Counting and finding greater then value in sql

Question

I'm supposed to display the name of the department in which there are at least four employees. I tried a few different methods, including this one:

SELECT Department, COUNT(EmployeeID) AS NumberOfEmployee
FROM Deparment d INNER JOIN Employees e
ON d.DeparmentID = e.DeparmentID
GROUP BY Deparment
HAVING NumberOfEmployee >= 4;

When I run the query, it is asking me to enter the parameter value for NumberOfEmployee. Can anyone tell me what's wrong?

I also thought I can't but somewhere is working. Do you have any other solution? — Mplus, Jun 16 '14 at 15:50
It's a limitation of SQL in general, not only of access. As far as I remember, the Having clause is performed before the Select clause, and as such, it does not know the aliases given in the select clause. — Zohar Peled, Jun 16 '14 at 15:50
[see here](http://stackoverflow.com/questions/2068682/why-cant-i-use-alias-in-a-count-column-and-reference-it-in-a-having-clause) — vhadalgi, Jun 16 '14 at 15:51
@ZoharPeled Yes, that's quite correct, sorry. It's simply a "feature" of MySQL ([demo](http://sqlfiddle.com/#!9/63eca/1)) — lc., Jun 16 '14 at 15:55

score 2 · Accepted Answer · answered Jun 16 '14 at 15:49

2

Try this instead:

SELECT Department, COUNT(EmployeeID) AS NumberOfEmployee
FROM Deparment d INNER JOIN Employees e
ON d.DeparmentID = e.DeparmentID
GROUP BY Deparment
HAVING COUNT(EmployeeID) >= 4;

Your original HAVING clause was invalid (you cannot use an alias in it).

answered Jun 16 '14 at 15:49

László Koller

1,139
6
15

@Mplus: On StackExchange sites, like StackOverflow (aka. SO), it's considered proper etiquette to mark the correct answer as the 'accepted answer'. Both to show all other SO readers that your question has been answered as well as to give the individual who spent time to answer your question credit for taking the time to do so. Welcome to StackOverflow! – László Koller Jun 16 '14 at 15:59

Counting and finding greater then value in sql

1 Answers1