CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Non Reg
Non Reg

Reputation: 19

SQL Query - Unsure How to Fix Logical Error

Edit: Sorry! I am using Microsoft SQL Server.

For clarification, you can have a department named "x" with a list of jobs, a department named "y" with a different list of jobs, etc.

I also need to use >= ALL instead of TOP 1 or MAX because I need it to return more than one value if necessary (if job1 has 20 employees, job2 has 20 employees and they are both the biggest values, they should both return).

In my query I'm trying to find the most common jobTitle and the number of employees that work under this jobTitle, which is under the department 'Research and Development'. The query I've written consists of joins to be able to return the necessary data.

The problem I am having is with the WHERE statement. The HAVING COUNT(JobTitle) >= ALL is finding the biggest number of employees that work under a job, however the problem is that my WHERE statement is saying the Department must be 'Research and Development', but the job with the most amount of employees comes from a different department, and thus the output produces only the column names and nothing else.

I want to redo the query so that it returns the job with the largest amount of employees that comes from the Research and Development department.

I know this is probably pretty simple, I'm a noob :3 Thanks a lot for the help!

SELECT JobTitle, COUNT(JobTitle) AS JobTitleCount, Department
FROM HumanResources.Employee AS EMP JOIN 
     HumanResources.EmployeeDepartmentHistory AS HIST
     ON EMP.BusinessEntityID = HIST.BusinessEntityID JOIN
     HumanResources.Department AS DEPT
     ON HIST.DepartmentID = DEPT.DepartmentID
WHERE Department = 'Research and Development'
GROUP BY JobTitle, Department
HAVING COUNT(JobTitle) >= ALL (
  SELECT COUNT(JobTitle) FROM HumanResources.Employee
  GROUP BY JobTitle 
)

Upvotes: 0

Views: 97

Answers (2)

shawnt00
shawnt00

Reputation: 17915

with employee_counts as (
    select
        hist.DepartmentID, emp.JobTitle, count(*) as cnt,
        case when dept.Department = 'Research and Development' then 1 else 0 end as is_rd,
    from HumanResources.Employee as emp
         inner join HumanResources.EmployeeDepartmentHistory as hist
             on hist.BusinessEntityID = emp.BusinessEntityID
         inner join HumanResources.Department as dept
             on dept.DepartmentID = hist.DepartmentID
    group by
        hist.DepartmentID, emp.JobTitle
)
select * from employee_counts
where is_rd = 1 and cnt = (
    select max(cnt) from employee_counts
    /* where is_rd = 1 */ -- ??
);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If you only want one row, then a typical method is:

SELECT JobTitle, COUNT(*) AS JobTitleCount
FROM HumanResources.Employee AS EMP JOIN 
     HumanResources.EmployeeDepartmentHistory AS HIST
     ON EMP.BusinessEntityID = HIST.BusinessEntityID JOIN
     HumanResources.Department AS DEPT
     ON HIST.DepartmentID = DEPT.DepartmentID
WHERE Department = 'Research and Development'
GROUP BY JobTitle
ORDER BY  COUNT(*) DESC
FETCH FIRST 1 ROW ONLY;

Although FETCH FIRST 1 ROW ONLY is the ANSI standard, some databases spell it LIMIT or even SELECT TOP (1).

Note that I removed DEPARTMENT both from the SELECT and the GROUP BY. It seems redundant.

And, if I had to guess, your query is going to overstate results because of the history table. If this is the case, ask another question, with sample data and desired results.

EDIT:

In SQL Server, I would recommend using window functions. To get the one top job title:

SELECT JobTitle, JobTitleCount
FROM (SELECT JobTitle, COUNT(*) AS JobTitleCount,
             ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM HumanResources.Employee AS EMP JOIN 
           HumanResources.EmployeeDepartmentHistory AS HIST
           ON EMP.BusinessEntityID = HIST.BusinessEntityID JOIN
           HumanResources.Department AS DEPT
           ON HIST.DepartmentID = DEPT.DepartmentID
      WHERE Department = 'Research and Development'
      GROUP BY JobTitle
     ) j
WHERE seqnum = 1;

To get all such titles, when there are duplicates, use RANK() or DENSE_RANK() instead of ROW_NUMBER().

Upvotes: 1

Related Questions