Oracle Grouping and Duplicate Checking

Question

I've been scratching my head on this one.

This question got me closer but my situation is a little more complex. How do I find duplicate values in a table in Oracle?

Suppose I have a table EMPLOYEE and I want to know which employees work in multiple departments. My table has the employee id, and department they work in. When an employee works in multiple departments, their employee id will be listed as multiple records. I don't want to just count the employees that are listed twice. I need to know how many work in this list of departments vs this list of departments.

So for example if my table is:

 Employee ID | Department
 1             Accounting
 1             Marketing
 2             Accounting
 3             Finance
 4             Programming

And Department List A is

Accounting
Finance

And Department List B is

Marketing
Programming

Then the results of the query would be

 Employee ID | Department
 1             Accounting
 1             Marketing

or

 Employee ID | Count(Department)
 1             2

since employee 1 works in a department from List A and List B.

The lists of departments that you're comparing is what changes? — Hart CO, Jan 15 '16 at 16:54
I think you should add a few more examples to clarify exactly what you want. Including the input parameter, and preferably a single expected output format. 2 or 3 examples like that would make your question much simpler. — Gergely Bacso, Jan 15 '16 at 16:56

score 2 · Accepted Answer · answered Jan 15 '16 at 17:06

I would approach this with a mapping:

with mapping as (
      select 'Accounting' as department, 'A' as grouping from dual union all
      select 'Finance' as department, 'A' as grouping from dual union all
      select 'Marketing' as department, 'B' as grouping from dual union all
      select 'Programming' as department, 'B' as grouping from dual
     )
select employeeid, count(distinct m.grouping)
from t join
     mapping m
     on t.department = m.department
group by employeeid;

Note: you may want to store this information in another table or as an attribute in the Departments table.

If you want solo departments to map to themselves:

select t.employeeid, count(distinct coalesce(m.grouping, t.department))
from t join
     mapping m
     on t.department = m.department
group by t.employeeid;

If you want other departments not in the table to be grouped together:

select t.employeeid, count(distinct coalesce(m.grouping, 'UNKNOWN MAPPING'))
from t join
     mapping m
     on t.department = m.department
group by t.employeeid;

score 1 · Answer 2 · answered Jan 15 '16 at 17:01

If I understand what you're after, you can use conditional aggregation in a HAVING clause for this:

SELECT Employee_ID, COUNT(DISTINCT Department) AS Dept_Count
FROM YourTable
GROUP BY Employee_ID
HAVING MAX(CASE WHEN Department IN ('Accounting','Finance') THEN 1 ELSE 0 END) = 1
  AND  MAX(CASE WHEN Department IN ('Marketing','Programming') THEN 1 ELSE 0 END) = 1

Oracle Grouping and Duplicate Checking

2 Answers2