Solved

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

Posted on 2014-03-17
6
218 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Recursion schedule 13 35
Substring works but need to tweak it 14 31
MS SQL Conditional WHERE clause 3 38
SQL Query Syntax error after > 11 41
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 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