MySQL Syntax for distinct a,b + b,a combinations
Hello,
I have a tricky SQL statement that I need to get and I was hoping you guys would help me. I have two integer fields, let's call them a & b. I need an SQL statement that returns only those combinations of a and b (in either order) that are in the database more than once (and the number of times they are in there). Here's an example:
Example Values:
A,B
1,2
2,3
1,9
2,3
3,2
1,4
8,1
1,5
5,1
1,4
I need the following returned
Value1, Value2, Count
2,3,3
1,5,2
1,4,2
I hope everyone can understand that. The pair of 2 and 3 have a count of 3 because they are there twice as a,b and once as b,a. The pair of 1 and 5 have a count of 2 because they are there once as a,b and once as b,a. The pair of 1 and 4 have a count of 2 because they are there twice as a,b. The value pairs of 1,2 1,9 1,8 are not returned because their total count would only be 1.
I already have the following SQL statement syntax for finding if they are more than 1 combo of a & b, but have not been able to get a count on if they are also b&a:
SELECT DISTINCT a,b COUNT(a) AS num FROM table GROUP BY a,b HAVING COUNT(a) > 1 ORDER BY num DESC
Thanks,
Matt
Raleigh, NC