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

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

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

thanx

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

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).

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.

The soln given does not seem to work,

error says group function not allowed in where clause

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

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;

itS NOT WORKING AND HOW TO SELECT DUPLICATE RECORDS.

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

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

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

Here vFirstName is not primarykey.

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

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

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

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


The above query is working properly.

$countDuplicate = mysql_query( "SELECT sal, COUNT(*) AS count FROM  employee GROUP BY sal HAVING COUNT(*) > 1" );

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

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;

Thanks