Solved

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

Posted on 2014-03-17
6
217 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 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 143

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

809 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