rajendar

 Profile Answers by 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

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

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

Culver_lake

 Profile Answers by 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.

veena

• Aug 23rd, 2006

 

The soln given does not seem to work,

error says group function not allowed in where clause

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

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;

hasanvtu

 Profile Answers by hasanvtu Questions by hasanvtu

• Dec 27th, 2006

 

itS NOT WORKING AND HOW TO SELECT DUPLICATE RECORDS.

Deepak

• Jan 31st, 2007

 

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

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

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

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

Roshni

• Oct 5th, 2007

 

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

The above query is working properly.

prisharma

 Profile Answers by prisharma 

• Dec 17th, 2007

 

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

sampra

 Profile Answers by 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

drseervi

 Profile Answers by 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