SQL Server: avg rate for male and female employees in AdventureWorks

Question

I'm trying to write a query to find the average pay rate for male and female employees in the AdventureWorks database

I wrote this (below), but I am not getting the desired results:

with sub as 
(
   select 
       emp.Gender, emp.VacationHours, pay.Rate
   from 
       HumanResources.Employee emp, HumanResources.EmployeePayHistory pay
   where 
       emp.BusinessEntityID = pay.BusinessEntityID
)
select 
    sub.Gender,
    avg(sub.VacationHours) as vac_hours, 
    avg(sub.Rate) as rate
from 
    sub
group by 
    sub.Gender, Rate;

I'm trying to do this so I can get a better understanding of how functions work

Answer 1

Just group by gender alone - not by gender and rate :

with sub AS
(
   select 
       emp.Gender, emp.VacationHours, pay.Rate
   from 
       HumanResources.Employee emp
   inner join 
       HumanResources.EmployeePayHistory pay on emp.BusinessEntityID = pay.BusinessEntityID
)
select 
    sub.Gender,
    avg(sub.VacationHours) as vac_hours, 
    avg(sub.Rate) as rate
from 
    sub
group by 
    sub.Gender;