CODEWITHSUNDEEP
sqldatabaseoracle-database
saroj
saroj

Reputation: 653

SQL to find rows where two columns have the same value

I have 3 columns in Oracle database having table mytable and i want records having only duplicate values in 2nd and 3rd column.

SQL> select * from mytable ;

column1      column2       column3

  A            50           50----required output
  A            10           20----have different values i.e. 10 and 20
  A            50           50----required output
  A            30           70----have different values i.e. 30 and 70
  B            20           20----required output
  B            40           30----have different values i.e. 40 and 30

I want the following output with count(*):

 column1      column2       column3

  A            50           50
  A            50           50
  B            20           20

Any help is much appreciated

Upvotes: 5

Views: 17402

Answers (3)

Jayesh Sorathia
Jayesh Sorathia

Reputation: 1614

Here are sample example , i am doing this SQL Server but i am sure this query work in ORACLE also EXAMPLE :

Create table #Test (colA int not null, colB int not null, colC int not null, id int not null identity) on [Primary]
GO
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)

INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)
INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)
INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)

INSERT INTO #Test (colA,colB,colC) VALUES (4,5,6)
GO
Select * from #Test
GO

select count(colA) as tot_duplicate_count , colA ,colB ,colC  from #Test where id <=
(Select Max(id) from #Test t where #Test.colA = t.colA and
#Test.colB = t.colB and
#Test.colC = t.colC)
group by colA ,colB ,colC 
having count(colA) > 1

This query this total count of duplicate record per data row

Upvotes: 0

user unknown
user unknown

Reputation: 36269

select column1, count (*)
from mytable
where column2 = column3
group by column1, column2;

Upvotes: 5

harry
harry

Reputation: 731

From your question it is not clear about primary key as A in First Column is being repeated many times.

You can try the following:

 select column1, column2, column3, count(*) from 
mytable where column2 = column3 group by column1, column2, column3;

Upvotes: 0

Related Questions