Search code examples
sqlsql-servert-sqlaggregatetemp-tables

SQL - How to return records only when both genders are present in the same job title?


Okay, so I've got a SQL query that is pulling employee information; job title, gender, pay rate, etc. I have a temp table present and am currently showing the average Pay Rate. Great.

Now I need to bring in the Job Title and keep the average pay rate but only limit the results where both Males and Females are present in the Job Title. Help?

All help is great appreciated!

Also, Is there anyway to create a new column that shows the difference in pay between Males and Females?

If EXISTS (Select * FROM TempDB..SYSOBJECTS WHERE name =  '##JTemp')
BEGIN
    DROP TABLE ##JTemp
END
GO

SELECT
    e.EmployeeID AS 'Employee ID',
    c.FirstName + ' ' + c.LastName AS 'Employee Name',
    e.Gender AS 'Employee Gender',
    e.Title AS 'Job Title',
    edh.DepartmentID,
    d.Name AS 'Department Name',
    MAX(eph.Rate) AS 'Pay Rate',
    eph.PayFrequency AS 'Pay Frequency',
    MAX(eph.Rate) * eph.PayFrequency AS 'Rate x Frequency'
INTO
    ##JTemp
FROM
    HumanResources.Employee e
INNER JOIN 
    Person.Contact c on c.ContactID = e.EmployeeID
INNER JOIN 
    HumanResources.EmployeeDepartmentHistory edh on            
edh.EmployeeID = e.EmployeeID
INNER JOIN 
    HumanResources.Department d on d.DepartmentID = edh.DepartmentID
INNER JOIN 
    HumanResources.EmployeePayHistory eph on eph.EmployeeID = e.EmployeeID
WHERE
    edh.EndDate IS NULL
GROUP BY 
    e.EmployeeID, c.FirstName, c.LastName,
    e.Gender, e.Title, edh.DepartmentID,
    d.Name, eph.PayFrequency

SELECT
    [Job Title],
    [Employee Gender],
    AVG([Pay Rate]) AS 'Average Pay Rate',
    AVG([Rate x Frequency]) AS 'Average Rate x Frequency'
FROM
    ##JTemp
GROUP BY 
    [Job Title], [Employee Gender]
ORDER BY 
    [Job Title]

--WHERE [Employee Gender] = 'M'

IF EXISTS (Select * FROM TEMPDB..SYSOBJECTS WHERE name = '##JTemp')
BEGIN
    DROP TABLE ##JTemp
END
GO

Solution

  • select m.[Job Title], m.ap, m.mp, f.ap, f.mp
     from (  Select [Job Title], AVG([Pay Rate]) as ap, max([Pay Rate]) as mp
               From ##JTemp
              Where [Employee Gender] = 'M' 
              Group By [Job Title] ) m
     join (  Select [Job Title], AVG([Pay Rate]) as ap, max([Pay Rate]) as mp
               From ##JTemp
              Where [Employee Gender] = 'F' 
              Group By [Job Title] ) f 
       on m.[Job Title] = f.[Job Title]