CODEWITHSUNDEEP
sqlsql-server
NYCHitman1
NYCHitman1

Reputation: 3

Need to calculate payroll for a few employees in SQL (across two tables)

So far I have 

SELECT DISTINCT
    ed.FirstName,
    ed.LastName,
    ed.Salary
FROM EmployeeData ed
INNER JOIN JobTitle jt
    ON ed.JobID = jt.JobID
    AND jt.ExemptNonExemptStatus = 'Exempt'
ORDER BY ed.Salary

However, I'm uncertain as to what is left to add the salaries together. I imagine a SUM function is in order, but every time I try something - it errors out. Can anyone help?

Upvotes: 0

Views: 459

Answers (2)

Sparky
Sparky

Reputation: 15105

Try this

 SELECT 
        ed.FirstName,
        ed.LastName,
        SUM(ed.Salary) as SalaryPerEmployee
    FROM EmployeeData ed
    INNER JOIN JobTitle jt ON ed.JobID = jt.JobID
                           AND jt.ExemptNonExemptStatus = 'Exempt'
    GROUP BY ed.FirstName,  ed.LastName,
    ORDER BY SalaryPerEmployee;

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280459

Assuming you just want a sum of all of the exempt employees, and don't need to have first name and last name in the result set (which I don't even know which one(s) you would list):

SELECT Salary = SUM(ed.Salary)
FROM dbo.EmployeeData AS ed
WHERE EXISTS
(
  SELECT 1 FROM dbo.JobTitle
    WHERE JobID = ed.JobID 
    AND ExemptNonExemptStatus = 'Exempt'
);

Upvotes: 0

Related Questions