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

  
Showing Answers 1 - 7 of 7 Answers

rajendar

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

thanx

  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

culver_lake

  • 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

Culver_lake

  • 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

veena

  • 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

Mukesh

  • 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

Deepak

  • Jan 31st, 2007
 

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

  Was this answer useful?  Yes

venkat

  • 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

prashanth023

  • May 24th, 2007
 

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

manikandan

  • 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

sree

  • 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

Roshni

  • 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

sampra

  • 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

drseervi

  • 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;

Thanks

  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