CODEWITHSUNDEEP
sqloracle-database
swetha reddy
swetha reddy

Reputation: 201

Count duplicates in two different columns having same name using SQL query

I have the following table:

Team1  Team2  result
---------------------
 a       b      a
 b       c      b
 c       a      a
 b       a      b
 b       c      -
 a       b      a
 b       c      b

I want to count duplicate combination of teams present in Team1 and Team2 columns as below:

Team1  Team2   count
--------------------
 a       b      3
 b       c      3
 c       a      1

I tried the following query:

SELECT TEAM1,TEAM2, COUNT(*) AS COUNT
FROM TABLE_NAME
GROUP BY TEAM1, TEAM2

I did not get the desired output. I got the following instead:

Team1  Team2   Count
---------------------
  c      a       1
  b      a       1
  a      b       2
  b      c       3

Could anybody help please?

Upvotes: 2

Views: 272

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You might use grouping by multiplication( being a commutative algebraic operation ) of ascii function applied to those columns as 

 with table_name(Team1,Team2,result) as
 (
     select 'a','b','a'  from dual union all
     select 'b','c','b'  from dual union all
     select 'c','a','a'  from dual union all
     select 'b','a','b'  from dual union all    
     select 'b','c',null from dual union all
     select 'a','b','a'  from dual union all
     select 'b','c','b'  from dual
 )
  select min(team1) as team1,max(team2) as team2,
         count(*) as count
    from table_name
   group by ascii(team1)*ascii(team2)  
   order by team1;

TEAM1 TEAM2 COUNT
  a     b     3
  b     c     3
  c     a     1

Rextester Demo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use least() and greatest():

SELECT LEAST(TEAM1, TEAM2) as TEAM1, GREATEST(TEAM1, TEAM2) as TEAM2, COUNT(*) AS CNT
FROM  TABLE_NAME
GROUP BY LEAST(TEAM1, TEAM2), GREATEST(TEAM1, TEAM2);

Upvotes: 4

Related Questions