Solved

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

Posted on 2014-03-17
6
215 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
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 142

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 142

Expert Comment

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

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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

895 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now