"Invalid column name" when trying to alias a result of COUNT function

Question

Hi I use sql server and I got this error when trying to execute a following query:

select 
e.dept_id, count(e.dept_id) as empCount from employee e
group by e.dept_id
having empCount > 0;

Invalid column name 'empCount'.

When I write it like this:

having e.dept_id > 0;

It works but I want to replace it with alias empCount.

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Mar 14 '18 at 13:25
The only place you can reference the alias of a column is in the `ORDER BY` clause. Have a look at the documentstion; specifically Logical Processing. https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql — Thom A, Mar 14 '18 at 13:43
Possible duplicate of [How to reuse calculated column in HAVING clause in SQL Server?](https://stackoverflow.com/questions/46963965/how-to-reuse-calculated-column-in-having-clause-in-sql-server) — Md. Suman Kabir, Mar 14 '18 at 13:43

score 1 · Answer 1 · edited Mar 14 '18 at 13:37

1

use having like below

select 
e.dept_id, count(e.dept_id) as empCount from employee e
group by e.dept_id
having count(e.dept_id) > 0;

edited Mar 14 '18 at 13:37

Tab Alleman

answered Mar 14 '18 at 13:31

padh

score 0 · Answer 2 · answered Mar 14 '18 at 13:25

0

Wrap the GROUP BY part up in a derived table, then you can use empCount:

select dept_id, empCount
from
(
    select e.dept_id, count(e.dept_id) as empCount
    from employee e
    group by e.dept_id
)
where empCount > 0

answered Mar 14 '18 at 13:25

jarlh

alternative: group by e.dept_id having count(e.dept_id) > 0 – heringer Mar 14 '18 at 13:27

2 Answers2