CODEWITHSUNDEEP
sqlsql-servert-sqlaggregatetemp-tables
Ken Wright
Ken Wright

Reputation: 55

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

Upvotes: 0

Views: 346

Answers (2)

Daniel
Daniel

Reputation: 174

Select
    j1.[Job Title],
    AVG(j1.[Pay Rate]) AS 'Average Pay Rate',
    AVG(j1.[Pay Frequency]) AS 'Average Rate x Frequency',
    (
        (Select AVG(j2.[Pay Rate])
        From ##JTemp j2
        Where j2.[Employee Gender] = 'M' 
            and j2.[Job Title] = J1.[Job Title]
        Group By
            j2.[Job Title])
        -
        (Select AVG(j2.[Pay Rate])
        From ##JTemp j2
        Where j2.[Employee Gender] = 'F' 
            and j2.[Job Title] = J1.[Job Title]
        Group By
            j2.[Job Title])
    ) as [Pay Rate Diff]
From 
    ##JTemp j1

Group By
    j1.[Job Title]
HAVING COUNT(DISTINCT j1.[Employee Gender]) = 2
Order By
    j1.[Job Title]

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

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]

Upvotes: 1

Related Questions