How we can count duplicate entry in particular table against Primary Key ? What are constraints?

Showing Answers 1 - 17 of 17 Answers


  • Mar 1st, 2006

select count(*) from tablename where count(*) >1  group by empno;

( jst checkin for to count duplicate records if not  jst send reply to me)


  Was this answer useful?  Yes

bhanu pratap kedia

  • Mar 17th, 2006

i want to know how u will find primary key in duplicate data .can we find a primary key in duplicate data.....

  Was this answer useful?  Yes


  • Mar 19th, 2006

Every table should have a primary key defined. If that is the case the RDBMS will not allow a duplicate row to be inserted. The INSERT statement will fail and the table will never have a duplicate (neither will the unique index).

  Was this answer useful?  Yes


  • Mar 19th, 2006

The syntax in the previous answer (where count(*) > 1) is very questionable. suppose you think that you have duplicate employee numbers. there's no need to count them to find out which values were duplicate but the followin SQL will show only the empnos that are duplicate and how many exist in the table:

Select empno, count(*)

from    employee

group by empno

having count(*) > 1

Generally speaking aggregate functions (count, sum, avg etc.) go in the HAVING clause. I know some systems allow them in the WHERE clause but you must be very careful in interpreting the result.  WHERE COUNT(*) > 1 will absolutely NOT work in DB2 or ORACLE.  Sybase and SQLServer is a different animal.

  Was this answer useful?  Yes


  • Aug 23rd, 2006

The soln given does not seem to work,

error says group function not allowed in where clause

  Was this answer useful?  Yes

Ed DiGiambattista

  • Aug 25th, 2006

That's exactly what I said. You can't have any aggregate function in a where clause. They must be in a having clause prededed by group by.

The approach is wrong anyway.

Select empno

from   emp

group by empno

having count(*) > 1

  Was this answer useful?  Yes


  • Sep 6th, 2006

select count(*) from tablename where count(*) >1 group by empno;this query generate an error use right syntex groupbyI have a table name -> empfields -> id (int autoincremented),name (varchar),sal(int)used query ->select count(*) from emp where count(*) >1 group by id;

  Was this answer useful?  Yes


  • Jan 31st, 2007

ySQL said: Documentation#1111 - Invalid use of group function

  Was this answer useful?  Yes


  • May 21st, 2007

Mysql supports count(*) and Count

Using Mysql count is retrieving the all not null record in the table
count(*) gives the all record including not null and null record in the table

  Was this answer useful?  Yes


  • May 24th, 2007

we can count the number of duplicated rows by using the following condition

Here vFirstName is not primarykey.

count( * )
FROM member
GROUP BY vFirstName
HAVING count( vFirstName ) >1
LIMIT 0 , 30


  • Jul 13th, 2007

You cannot get the result from group by statement  without adding all columns from the table.
assume the table contain only 2 columns.
Ex: select colmn1, colmn2 from tab group by colmn1,colmn2

  Was this answer useful?  Yes


  • Jul 17th, 2007

This is working, thanks. This will give all uniq records of that column.
select column_name from tab group by column_name

  Was this answer useful?  Yes


  • Oct 5th, 2007

SELECT feildname, COUNT(*) FROM t1 GROUP BY name HAVING COUNT(*) >1;

The above query is working properly.

  Was this answer useful?  Yes


  • Feb 14th, 2008

i want to know how u will find primary key in duplicate data .can we find a primary key in duplicate data

  Was this answer useful?  Yes


  • May 23rd, 2009

1. Primary key does not allow duplocate.
2. We can use below query to find duplicate records

select count(*),classifiedID  as tot  from jicka_photos  group by classifiedID  HAVING count(classifiedID) >1 limit 5;


  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions