Finding Duplicate Rows in a Table

Question

I am trying to find out how many duplicate records I have in a table. I can use count, but I'm not sure how best to eliminate records where the count is only 1.

select first_name, last_name, start_date, count(1)
from employee
group by first_name, last_name, start_date;

I can try to order by the count, but I am still not eliminating those with a count of one.

Answer 1

you can use having clause as having Count(*) > 1 after group by like this :

select 
  first_name, 
  last_name, 
  start_date,
  Count(*) AS Count
from 
  employee 
group by 
  first_name, 
  last_name, 
  start_date
having 
  Count(*) > 1