• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

Need help understanding aggragates, subqueries and the HAVING clause.

I get this error message:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

When I run this query:

SELECT     dbo.Company.Company_name
FROM         dbo.Company INNER JOIN
                      dbo.Company_Division ON dbo.Company.Company_id = dbo.Company_Division.Company_id
where count(dbo.Company_Division.division_id) > 1

What does this mean?  I simply want to return the company names that have more than one division.

Thanks
Aaron.
0
abenage
Asked:
abenage
  • 2
2 Solutions
 
rafranciscoCommented:
Try this:

SELECT     dbo.Company.Company_name
FROM         dbo.Company INNER JOIN
                      dbo.Company_Division ON dbo.Company.Company_id = dbo.Company_Division.Company_id
GROUP BY dbo.Company.Company_name
HAVING COUNT(*) > 1
0
 
rafranciscoCommented:
Based on your error message, you cannot have an aggregate function inside your WHERE clause.  If you want to filter out records from the result of a GROUP BY and the filter uses aggregate functions such as COUNT() and SUM(), you should do it in the HAVING clause of the SELECT statement.
0
 
abenageAuthor Commented:
That was it.  Thanks.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now