SQL SELECT Query

Question

Suppose I have a SQL table "Company" with three columns: "department_id", "employee", "job". Something like this:

DEPARTAMENT_ID | EMPLOYEE | JOB
--------------------------------------
1              | Mark     | President
1              | Robert   | Marketing Manager
1              | Rose     | Administration Assitant
2              | Anna     | Programmer
2              | Michael  | Programmer
2              | Celia    | Sales Manager
3              | Jhon     | Sales Manager
3              | Donna    | Programmer
3              | David    | Marketing Manager

I would like to write a query that returns the departments id where at least 50% of their jobs are the same.

Result i need in my example would be just:

DEPARTAMENT_ID |
--------------------------------------
2              |

How do I write this SQL query? I think i tried all kind of stuff but i dont get it :(.

Answer 1

This is a bit tricky. You need to compare the total number of people on a job in a department to the total number. So, one method uses two aggregations:

select department_id
from (select department_id, count(*) as numemp
      from t
      group by department_id
     ) d join
     (select department_id, max(numemp) as numemp
      from (select department_id, job, count(*) as numemp
            from t
            group by department_id, job
           ) d
     group by department_id
    ) dj
    on d.numemp <= 2 * dj.numemp;

You might get duplicates if you have one department that is exactly split between two jobs. In that case, use select distinct.