SQL Query to return only duplicate rows and count

I have a table with duplicate names in it. Write me a query which returns only duplicate rows with number of times they are repeated?
  • Interview Candidate
  • 37
  • 6096

Showing Answers 1 - 37 of 37 Answers

ak rajput

  • Aug 9th, 2005
 

select col1,col2 from tab1 a where (col1,col2) in (select (col1,col2 from tab1 b where rowid<>(select max(rowid) from tab1 c where b.col1=c.col1 and b.col2=c.col2); 

  Was this answer useful?  Yes

Sudip Ray

  • Aug 9th, 2005
 

select name,count(name) occurences 
from table1 
group by name 
having count(name)>1

B.Vijay Karthik

  • Aug 25th, 2005
 

SELECT COL1 FROM TAB1  
WHERE COL1 IN  
(SELECT MAX(COL1) FROM TAB1  
GROUP BY COL1 
HAVING COUNT(COL1) > 1 )

  Was this answer useful?  Yes

PRATHIMA

  • Sep 9th, 2005
 

select count(COL1),COL1 from TABLE1 group by COL1

  Was this answer useful?  Yes

Ateesh

  • Sep 28th, 2005
 

select count(COL1),COL1 from TABLE1 group by COL1 having count(COL1)>1

Manju

  • Nov 11th, 2005
 

SELECT DISTINCT (column name) from TABLENAME

Used to return a dataset with unique entries for certain database table column

  Was this answer useful?  Yes

R Ravinder

  • Nov 16th, 2005
 

select column_name, count(*) from table_name group by column_nmae having count(*)>1

  Was this answer useful?  Yes

Prashanth Reddy

  • Aug 2nd, 2006
 

SELECT * FROM <TABLE_NAME>

WHERE ROWID NOT IN (SELECT MIN(ROWID)

FROM <TABLE_NAME> GROUP BY <col1>,<col2>)

  Was this answer useful?  Yes

Nitin Sharanagate

  • Sep 12th, 2006
 

select category ,count(category) count from tablename
group by category

Ouput

Initial Public Offerings (IPOs) 72

Secondary Offerings 158 

  Was this answer useful?  Yes

JOBIN

  • Jun 19th, 2007
 

SELECT NA,CNT FROM (select NAME NA,COUNT(SALESORDERITEM) CNT from TABLE

GROUP BY NAME)A

WHERE A.CNT>1

  Was this answer useful?  Yes

Taj Alam

  • Aug 21st, 2007
 

select * from TABLENAME
WHERE  NAMES IN
                           (SELECT NAMES FROM TABLENAME
                                         WHERE  COUNT(NAMES)>1);



NOTES : NAMES  is the column according to which it decides the records are duplicate. taj_email @ yahoo . co . in

  Was this answer useful?  Yes

mayank1710

  • Mar 25th, 2009
 

SELECT acct_nbr, count(acct_nbr) AS count FROM acct_cycle GROUP BY acct_nbr
HAVING count(acct_nbr)>1;
This query will solve your need.
Also if you want to display the complete row that contains the duplicate account
numbers then you can also try this query.
SELECT * FROM acct_cycle WHERE acct_nbr IN (SELECT acct_nbr FROM acct_cycle
GROUP BY acct_nbr HAVING count(acct_nbr)>1);


  Was this answer useful?  Yes

jpundalik

  • Dec 30th, 2009
 

Below query gives us name (which is duplicated) along with number of duplicates.

select name,count(1) from emp
group by name having count(1) > 1;

  Was this answer useful?  Yes

Sunil Charde

  • May 3rd, 2011
 

CREATE TABLE IF NOT EXISTS `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ;


INSERT INTO `mytable` (`id`, `name`, `value`) VALUES
(1, 'sunil', 123),
(2, 'ajay', 125),
(3, 'rajesh', 145),
(4, 'sunil', 258),
(5, 'sunil', 100),
(6, 'samir', 200),
(7, 'rajesh', 500),
(8, 'ajay', 200),
(9, 'sumit', 100),
(10, 'ajay', 300),
(11, 'ganesh', 560),
(12, 'dipak', 789),
(13, 'ganesh', 234),
(14, 'rahul', 250),
(15, 'dipak', 123),
(16, 'sunil', 756);


SELECT COUNT( * ) , `id` , `name` , `value` 
FROM mytable
GROUP BY `name` 
HAVING COUNT( * ) >1

  Was this answer useful?  Yes

rohit1505

  • May 21st, 2011
 

SELECT *
FROM emp
WHERE rowid NOT IN (SELECT MIN(rowid)
FROM emp
GROUP BY ename);

(OR)

SELECT empno,ename
FROM emp
GROUP BY empno, ename
HAVING count(ename)>1;

  Was this answer useful?  Yes

Give your answer:

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

 

Related Answered Questions

 

Related Open Questions