January 19, 2016 at 5:31 pm
subquery +Aggregate Functions
--use [AdventureWorksDW2012] [dbo].[DimEmployee]
--display DepartmentName ,Average baseRate for each Department
--only show a list of deps that have Female Employees whose average base Rates are more than the maximum baseRate of Current Status employees in Human Resources department
Here is my query:
use [AdventureWorksDW2012]
go
select [DepartmentName],avg([BaseRate]) as'Average BaseRate',[Gender],[status]
from [dbo].[DimEmployee] D1
where 'Average BaseRate' in (select [BaseRate]
from [dbo].[DimEmployee]
having avg([BaseRate])>max([BaseRate]))
and [DepartmentName] = 'Human Resources'and [Gender]= 'F' and [Status]='Current'
Group by [DepartmentName],[Gender],[Status]
I got an error message:
Msg 8120, Level 16, State 1, Line 4
Column 'dbo.DimEmployee.BaseRate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Please help!
January 19, 2016 at 7:23 pm
Resolved.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply