Solved

Solving error message 'An aggregate may not appear in the WHERE clause'

Posted on 2014-03-17
6
220 Views
Last Modified: 2014-03-17
Hi guys,

As title says im getting that error message, I know why im getting it but what is the best work around? Ive tried a few but must be getting the syntax wrong.

SELECT     PartNumber, Manufacture, COUNT(PartNumber) as TheCount
FROM         CounterfeitParts
WHERE      COUNT(PartNumber) > 1
Group BY PartNumber, Manufacture

Thanks,
Dean
0
Comment
Question by:deanlee17
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39933801
SELECT     PartNumber, Manufacture, COUNT(PartNumber) as TheCount
FROM         CounterfeitParts
HAVING COUNT(PartNumber) > 1
Group BY PartNumber, Manufacture

Open in new window

0
 

Author Comment

by:deanlee17
ID: 39933805
Incorrect syntax near the keyword 'Group'.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39933813
the correct syntax would be this:
SELECT     PartNumber, Manufacture, COUNT(PartNumber) as TheCount
FROM         CounterfeitParts
Group BY PartNumber, Manufacture
HAVING COUNT(PartNumber) > 1 

Open in new window

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39933814
however, doing a count(PartNumber) here is usually better COUNT(*) instead ...
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39934060
I know Guy (aka Scary Scar Puffy Shirt Dude) has already correctly answered your question, but if it helps I have an article out there on SQL Server Group By Solutions, and point #3 illustrates the HAVING clause.   If the article helps you, please click the 'Yes' button at the bottom.
0
 

Author Comment

by:deanlee17
ID: 39934065
Thanks Jim
0

Featured Post

Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question