GeekInterview.com
Series: Subject: Topic:
Question: 58 of 58

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

Asked by: Interview Candidate | Asked on: Feb 15th, 2006
Showing Answers 1 - 17 of 17 Answers
rajendar

Answered On : Mar 1st, 2006

View all answers by rajendar

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

  
Login to rate this answer.
bhanu pratap kedia

Answered On : 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.....

  
Login to rate this answer.
culver_lake

Answered On : 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).

  
Login to rate this answer.
Culver_lake

Answered On : Mar 19th, 2006

View all answers by Culver_lake

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.

  
Login to rate this answer.
veena

Answered On : Aug 23rd, 2006

The soln given does not seem to work,

error says group function not allowed in where clause

  
Login to rate this answer.
Ed DiGiambattista

Answered On : 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

  
Login to rate this answer.
Mukesh

Answered On : 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;

  
Login to rate this answer.

itS NOT WORKING AND HOW TO SELECT DUPLICATE RECORDS.

  
Login to rate this answer.
Deepak

Answered On : Jan 31st, 2007

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

  
Login to rate this answer.
venkat

Answered On : 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

  
Login to rate this answer.
prashanth023

Answered On : 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

Yes  1 User has rated as useful.
  
Login to rate this answer.
manikandan

Answered On : 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

  
Login to rate this answer.
sree

Answered On : 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

  
Login to rate this answer.
Roshni

Answered On : Oct 5th, 2007

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


The above query is working properly.

  
Login to rate this answer.
prisharma

Answered On : Dec 17th, 2007

View all answers by prisharma

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

  
Login to rate this answer.
sampra

Answered On : Feb 14th, 2008

View all answers by sampra

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

  
Login to rate this answer.
drseervi

Answered On : May 23rd, 2009

View all answers by drseervi

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

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Ads

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.