-2

Assume all of the table and field names are correct. I've tried this a few different ways but this is the only that makes sense needing the join in both. This is the question:

SQL query to get the list of job roles with more than 120 active employees, and sorted in descending order based on active count

SELECT JobRole, Count(EmployeeID)
FROM Job_Data 
LEFT JOIN Attrition_Data
    ON (Job_Data.EmployeeID = Attrition_Data.EmployeeID)
WHERE 

    (SELECT COUNT(EmployeeID) 
    FROM Job_Data 
    LEFT JOIN Attrition_Data
        ON (Job_Data.EmployeeID = Attrition_Data.EmployeeID)
    WHERE Attrition_Flag = 0
) > 120
AND AttritionFlag = 0
GROUP BY JobRole 
ORDER BY (EmployeeID) DESC

Any and all help is greatly appreciated.

5
  • 4
    Please edit your question and add what database server & version you are using and the table definitions. Thanks!
    – dwhitemv
    Oct 27 '21 at 23:29
  • 1
    Your example query is syntactically broken and won't execute. Please fix the syntax errors in it such as what looks like should be a comma in outer query's SELECT list (instead of a period), and returning only one field from your subquery.
    – J.D.
    Oct 28 '21 at 0:11
  • I wasn't given the db information, sorry I'm still pretty new to all of this. I don't know enough about SQL to know if I should be putting the aggregate somewhere else or if the working table and join set up is wrong but think that's the main question.
    – BFrank117
    Oct 28 '21 at 0:25
  • @BFrank117 As I mentioned, your query doesn't run, it has invalid code in it which are syntactical errors. Aside from that, knowing which database system you're using affects what answer is applicable to your question, so that is pertinent information, as dwhitemv mentioned. Please find out the database system or if this is a school-type of exercise then at least verify if the syntax you should be following is just straight ANSI-SQL or a particular implementation like Microsoft's T-SQL.
    – J.D.
    Oct 28 '21 at 12:18
  • Does this answer your question? How can I filter on result of aggregate functions like min, max, sum?
    – bbaird
    Oct 28 '21 at 13:30
1

Looks like you just need a HAVING clause.

  • COUNT(NotNullValue) is the same as COUNT(*)
  • LEFT JOIN can be INNER because we only want results with matching rows
  • I strongly suggest you use table aliases, and refer to columns using them
SELECT
  jd.JobRole,
  COUNT(*)
FROM Job_Data AS jd
INNER JOIN Attrition_Data AS ad
    ON jd.EmployeeID = ad.EmployeeID
WHERE 
    AttritionFlag = 0
GROUP BY
    jd.JobRole
HAVING
    COUNT(*) > 120 
ORDER BY
    COUNT(*) DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.