Getting Individual Records from SQL GROUP BY with Multiple Columns

Data and Databases
1

I’m working with a database table called Employees that has these fields:

Employee_ID (int) | Full_Name (varchar) | Years_Old (int) | Skill_Rating (int)

What I want to do is find all Employee_IDs where multiple people share the same combination of Years_Old and Skill_Rating.

Here’s what I’ve tried so far:

SELECT COUNT(*), Employee_ID FROM Employees 
GROUP BY Years_Old, Skill_Rating
HAVING COUNT(*) > 1

The problem is that this query groups the duplicate records together but doesn’t show me each individual Employee_ID that belongs to those duplicate groups.

I need to see all the Employee_IDs for records that aren’t unique based on the Years_Old and Skill_Rating combination. How can I modify this query to get all the individual IDs instead of just the count?

Any suggestions would be really helpful!

2

another approach would be using a subquery with EXISTS - something like SELECT Employee_ID FROM Employees e1 WHERE EXISTS (SELECT 1 FROM Employees e2 WHERE e1.Years_Old = e2.Years_Old AND e1.Skill_Rating = e2.Skill_Rating AND e1.Employee_ID != e2.Employee_ID) tho might be slower than window functions depending on your data size

3

The cleanest solution involves using your original GROUP BY approach in a subquery to identify the duplicate combinations, then joining back to get all individual records. Try this query: SELECT e.Employee_ID FROM Employees e INNER JOIN (SELECT Years_Old, Skill_Rating FROM Employees GROUP BY Years_Old, Skill_Rating HAVING COUNT(*) > 1) duplicates ON e.Years_Old = duplicates.Years_Old AND e.Skill_Rating = duplicates.Skill_Rating. This approach leverages your existing logic while efficiently retrieving all Employee_IDs that match the duplicate criteria. I have used this pattern extensively in production environments and it performs well with proper indexing on the grouped columns.

4

hmm interesting problem! have you considered using a window function instead? something like SELECT Employee_ID FROM (SELECT Employee_ID, COUNT(*) OVER (PARTITION BY Years_Old, Skill_Rating) as cnt FROM Employees) WHERE cnt > 1 might work better? curious what database system your using - does it support window functions?