CODEWITHSUNDEEP
sqloracle-database
Setsuna F. Seiei
Setsuna F. Seiei

Reputation: 302

SQL query in which count function is in where clause

I have table where I am tracking association between departments and budget centers. The association is many-to-many. Now I want to display only those departments which have more than 10 budget centers associated to them. SO my query should be something like this

select dept,
       count(budget_centers) as bcCount
from myTable
where bcCount > 10
group by dept

Now oracle will give an error saying "bcCount" is an invalid identifier. Is there a way to rephrase the query which will be acceptable in oracle

Upvotes: 0

Views: 190

Answers (2)

AirBorne
AirBorne

Reputation: 21

Try this 

select dept,
       count(budget_centers) as bcCount
from myTable
group by dept
having count(budget_centers) > 10

Upvotes: 2

user330315
user330315

Reputation:

That's what the having clause is for:

select dept,
       count(budget_centers) as bcCount
from myTable
group by dept
having count(budget_centers) > 10

Here are the relevant parts of the manual:

Upvotes: 5

Related Questions