SQL SELECT Query

Question

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

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 id of departments where at least 50% of their jobs are the same.

The result that I need in my example will be simple:

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

How to write this SQL query? I think I tried all kinds of things, but I do not understand: (.

Answer 1

This is a bit complicated. You need to compare the total number of people at work in the department with the total number. Thus, one method uses two aggregates:

 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 can get duplicates if you have one department that is precisely divided between two tasks. In this case, use select distinct .