Solved

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

Posted on 2014-03-17
6
216 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

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 …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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