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 set up sql*plus output format in oracle?
Does mysql support pl sql?
What is the current version of postgresql?
What are the different datatypes available in PL/SQL?
How you will create Toad Function?
Explain the difference between cursor declared in procedures and cursors declared in the package specification?
Where the integrity constrints are stored in Data Dictionary?
How can a function retun more than one value in oracle with proper example?
Is sql the best database?
Does inner join return duplicate rows?
how can we know the number of days between two given dates using mysql? : Sql dba
can i give user defined exception in a package