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.
Just a little logic switch should do the trick
Select Col1 ,Col2 ,Hits = sum(1) From ( Select Col1=case when col1<Col2 then Col1 else Col2 end ,Col2=case when col1<Col2 then Col2 else Col1 end from YourTable ) A Group By Col1,Col2
Col1 Col2 Hits Jay Jo 4 Linda Luke 3 Luke Wendy 1