Error in Select Query using Group by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error in Select Query using Group by

Author

Topic

sital
Yak Posting Veteran

89 Posts

Posted - 2009-05-11 : 11:59:54

I want to find the Rate of employees less than the average pay rate. I have written the following query but it is giving an error.

select EmployeeID,Rate from HumanResources.EmployeePayHistory having Rate < avg(Rate) group by EmployeeID

Error:

The following error message I receive

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'group'.

Can anybody tell me what is the error.?

Thanks in advance..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 12:07:09

[code]
select ph.EmployeeID,ph.Rate
from HumanResources.EmployeePayHistory ph
cross join (select AVG(Rate) AS AvgRate FROM HumanResources.EmployeePayHistory) t
where ph.Rate < t.AvgRate
[/code]

sital
Yak Posting Veteran

89 Posts

Posted - 2009-05-11 : 12:09:31

quote:

Originally posted by visakh16


select ph.EmployeeID,ph.Rate 
from HumanResources.EmployeePayHistory ph
cross join (select AVG(Rate) AS AvgRate FROM HumanResources.EmployeePayHistory) t
where ph.Rate < t.AvgRate

Thanks for your prompt reply.

The query is working exactly the way I wanted it.

Is it not possible to do the same thing using the having clause?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 12:30:47

you could also do it this way


select ph.EmployeeID,ph.Rate 
from HumanResources.EmployeePayHistory ph
where ph.Rate < (select AVG(Rate) AS AvgRate FROM HumanResources.EmployeePayHistory)

sital
Yak Posting Veteran

89 Posts

Posted - 2009-05-11 : 12:32:52

quote:

Originally posted by visakh16

you could also do it this way


select ph.EmployeeID,ph.Rate 
from HumanResources.EmployeePayHistory ph
where ph.Rate < (select AVG(Rate) AS AvgRate FROM HumanResources.EmployeePayHistory)

Sir, actually I have been given an assignment of doing the above query by using the having statement.

So Can you help me out with the query using having statement.?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 12:42:42

you can us having instead of where in above statement. i used where as having is used in places where we take aggregates.

sital
Yak Posting Veteran

89 Posts

Posted - 2009-05-11 : 12:46:54

quote:
Originally posted by visakh16

you can us having instead of where in above statement. i used where as having is used in places where we take aggregates.

But when I replace having with where it is displaying the following error message:

Msg 8121, Level 16, State 1, Line 2
Column 'HumanResources.EmployeePayHistory.Rate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Can You tell me what is the error?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 12:49:34

do you mean this?


select ph.EmployeeID,ph.Rate 
from HumanResources.EmployeePayHistory ph
having ph.Rate < (select AVG(Rate) AS AvgRate FROM HumanResources.EmployeePayHistory)

sital
Yak Posting Veteran

89 Posts

Posted - 2009-05-11 : 12:53:29

quote:

Originally posted by visakh16

do you mean this?


select ph.EmployeeID,ph.Rate 
from HumanResources.EmployeePayHistory ph
having ph.Rate < (select AVG(Rate) AS AvgRate FROM HumanResources.EmployeePayHistory)

Yes this query:

select ph.EmployeeID,ph.Rate
from HumanResources.EmployeePayHistory ph
having ph.Rate < (select AVG(Rate) AS AvgRate FROM HumanResources.EmployeePayHistory)
[/code]

displays the following error:
Msg 8121, Level 16, State 1, Line 2
Column 'HumanResources.EmployeePayHistory.Rate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

What is the error?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources