CODEWITHSUNDEEP
sqlsqlite
YohanRoth
YohanRoth

Reputation: 3253

How to find all rows that have same values but possibly permuted rows in SQL

Cannot figure our how to count all rows that have same values in any order. E.g in this example 

1| Jay   | Jo
2| Jay   | Jo
3| Jay   | Jo
4| Jo    | Jay
5| Linda | Luke
6| Linda | Luke
7| Luke  | Linda
8| Luke  | Wendy

I am looking for the following output:

Jay   | Jo    | 4
Linda | Luke  | 3
Luke  | Wendy | 1

I was killing myself over it for 2 days. Everything does not work. Joins lead to overcount, unions don't work as well. since they preserve entries with both orders ... I understand that I probably need to do some sort of self join and to consider two copies of the same table. But what manipulations to perform, I don't know! Really confused! Please help!

If possible I would appreciate answer in SQL Lite but probably any syntax would work. I just need to understand the logic how to compose the tables.

Thank you

Upvotes: 0

Views: 35

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

An easier way in SQLite would be to use least and greatest.

select least(col1,col2),greatest(col1,col2),count(*)
from tbl
group by least(col1,col2),greatest(col1,col2)

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81970

Just a little logic switch should do the trick

Example

Select Col1
      ,Col2
      ,count(*) as Hits
 From (
        Select case when col1<Col2 then Col1 else Col2 end as Col1
              ,case when col1<Col2 then Col2 else Col1 end as Col2
          from YourTable
      ) A
 Group By Col1,Col2

Returns

Col1    Col2    Hits
Jay     Jo      4
Linda   Luke    3
Luke    Wendy   1

Upvotes: 3

Related Questions