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 |
What is normalization? How many normalization forms are there?
Can we rename a column in the output of sql query?
Explain the purpose of %type and %rowtype data types with the example?
Can we write dml inside a function in sql server?
what is difference between stored procedures and application procedures,stored function and application function?
who introduced sql?
What is Overloading of procedures ?
What is the best free sql database?
How to revise and re-run the last sql command?
Hi, I am new in oracle(SQL), could anyone help me in writing a correct SQL. Below is the table structure. Table: Subsc Fields: 1. Sub_no (this field will hold values of subscriber nos, for e.g. S111111, S222222, S333333, S444444, etc.) 2. s_status (this field will hold values for different status of subscriber, for e.g. 'A', 'S', 'C', etc.) 3. cus_id (this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) Table: Bill Fields: 1. Bill_no this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) 2. b_status = (this field will hold values for different status of bill for e.g. 'O', 'C', 'S', etc.) Note: 1. The Sub_no is a Primary key of Subsc table. 2. The cus_id is a foreign in Subsc table (referred from Bill_no field of Bill table) 3. The Bill_no field is the Primary key of Bill table. Query A --> I wrote a query to select cus_id/Bill_no which is in status open (b_status = 'O') and having more than two active subscriber (i.e. S_status = 'A') in it ( i.e. more the two subscribers in same bill). select s.cus_id from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id having count(sub_no) = 2 Problem : The above query will give the cus_id (or rather bill_no) which are in open status (b_status ='O) and which are having TWO ACTIVE Subscribers (s_status ='A') in it. However, this query will also lists the cus_id/bill_no which are having more than TWO subscribers in it (but only two subscriber will be in Active status (s_status = 'A') and the others will be in s_status = 'C' or s_status = 'S'. Help needed: I want to write a query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it. B--> If I include the sub_no in the above query then NO row are returned. select s.cus_id, s.sub_no from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id, s.sub_no having count(sub_no) = 2 Help needed: I want to modify the above query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it ALONG with the sub_no. Thanks a lot in advance. Regards, Nitin
How is data stored in sql?
how to get enames with comma seperated values by deptwise on emp table?