CODEWITHSUNDEEP
sqlms-access
Joseph U.
Joseph U.

Reputation: 4607

SQL Comparison on Aggregate function

When I run the following SQL in ACCESS 2007

Select Location, COUNT(ApartmentBuildings) AS TotalIBuildingsManaged From Apartments Where COUNT(ApartmentBuildings) > 3 Group By Location Order By COUNT(ApartmentBuildings) DESC;

I get the following error:

Cannot have aggregate function in where clause. How should I be forming this query to get all of the locations which have a count of ApartmentBuildings greater than 3?

Upvotes: 4

Views: 5264

Answers (2)

codingbadger
codingbadger

Reputation: 44042

You need to use the HAVING clause

Select Location, COUNT(ApartmentBuildings) AS TotalIBuildingsManaged 
From Apartments 
Group By Location 
HAVING COUNT(ApartmentBuildings) > 3

Upvotes: 3

Elian Ebbing
Elian Ebbing

Reputation: 19067

Use having instead of where:

Select Location, COUNT(ApartmentBuildings) AS TotalIBuildingsManaged 
From Apartments 
Group By Location
Having COUNT(ApartmentBuildings) > 3  
Order By COUNT(ApartmentBuildings) DESC;

for more information see this page

Upvotes: 5

Related Questions