Solved

SQL Group By Error

Posted on 2014-10-18
4
151 Views
Last Modified: 2014-10-18
I get the following error in the SQL Statement below

Incorrect syntax near the keyword 'Group'.

Select Phone, [First Name], [Machine operator],Sum([Drilled Total]) As [Total Drilled]
From Performance Inner Join People On [Machine operator] = [Operator COY]
Having Sum([Drilled Total]) > 100 And [Date] >= DATEADD(day,-30, getdate())
Group By Phone,[First Name],[Machine operator]
0
Comment
Question by:murbro
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
AielloJ earned 250 total points
Comment Utility
murbro:

You're not allowed to have a non-aggregated expression ([Date] in this case) in a HAVING cluase.  Your [Date] expression must be specified in a WHERE clause.  Try the following:

SELECT
  [Phone],
  [First Name],
  [Machine operator],
  Sum([Drilled Total]) As [Total Drilled]
FROM
  Performance
 Inner Join
  People
 On [Machine operator] = [Operator COY]
WHERE
  {Date] >= DATEADD(day,-30, getdate())
HAVING
  Sum([Drilled Total]) > 100
GROUP BY
  [Phone],
  [First Name],
  [Machine operator]
 
I also suggest consistency in the use of the square brackets on column names as shown.

Best regards,

AielloJ
0
 

Author Comment

by:murbro
Comment Utility
Thanks. I am still getting the following error
Incorrect syntax near the keyword 'GROUP'.
0
 
LVL 22

Assisted Solution

by:Snarf0001
Snarf0001 earned 250 total points
Comment Utility
One modification from the above.  AielloJ is right, the non-grouped "date" column has to be in the where clause, not the having.
Only change, is "having" has to appear AFTER the group by:

Select Phone, [First Name], [Machine operator],Sum([Drilled Total]) As [Total Drilled]
From Performance Inner Join People On [Machine operator] = [Operator COY] 
Where [Date] >= DATEADD(day,-30, getdate())
Group By Phone,[First Name],[Machine operator] 
Having Sum([Drilled Total]) > 100

Open in new window

0
 

Author Closing Comment

by:murbro
Comment Utility
Thank you both
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

744 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

8 Experts available now in Live!

Get 1:1 Help Now