how do you count the duplicate records in a table
Answers were Sorted based on User's Feedback
Answer / priya
select coloumn_name ,count(*) from table_name
group by coloumn_name
having count(*) > 1;
Is This Answer Correct ? | 35 Yes | 0 No |
Answer / babu
Try this,
select col1,count(col1) from tab1
group by col1 having count(col1)>1;
If you want to delete these duplicate entries, use:
delete from tab1 where col1 in (select a.col1 from
(select col1,count(col1) from tab1
group by col1 having count(col1)>1) a);
Is This Answer Correct ? | 18 Yes | 1 No |
Answer / purushotham
select column,count(1) from a
group by column
having count(1)>1;
Is This Answer Correct ? | 7 Yes | 1 No |
Answer / dev
Hi,
There are many ways to do it.
One of that is ,
select count(0) from tab1 a
where a.rowid > any (select b.rowid from tab1 b where
a.col1 =b.col1);
Is This Answer Correct ? | 6 Yes | 4 No |
Answer / akki julak
select count(empno)-count(distinct(empno)) from emp;
Is This Answer Correct ? | 3 Yes | 2 No |
Answer / ajmal khan
There are many way to count the duplicate row in a table
select count(column_name)-count(distinct(column_name)) from
table_name;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / suresh a
select sum(count(col1) -1) from emp
group by col1 having count(col1) > 1
Is This Answer Correct ? | 2 Yes | 2 No |
Answer / shalu
select count(*) from table_name group by column1,column2...
having count(*) > 1
Is This Answer Correct ? | 2 Yes | 2 No |
Answer / sravan
Hi here is another way to solve this
SELECT NAME, COUNT(NAME) FROM TABLE_NAME
WHERE NAME IN (SELECT NAME FROM TABLE_NAME
GROUP BY NAME
HAVING COUNT(NAME)>1)
GROUP BY NAME;
thanks
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / satheesh
SELECT COUNT(*)
FROM TABLE A
WHERE ROWID NOT IN (SELECT MAX(ROWID)
FROM TABLE B
WHERE A.COL1 = B.COL1);--UNIQUE COLUMN
Is This Answer Correct ? | 0 Yes | 0 No |
How to write pl sql program in mysql command prompt?
write a qurey for finding the length of the sting.
What is blind sql injection?
What are % type and % rowtype?
How can I pass the inner procedure value to outer procedure ?
how to create a new table in mysql? : Sql dba
how can we transpose a table using sql (changing rows to column or vice-versa) ? : Sql dba
what are local and global variables and their differences? : Sql dba
What is trigger in sql? Explain
IF i write before / after insert / update trigger and i do rollback what will happen?
Is sql the best database?
Mention what plvcmt and plvrb does in pl/sql?