Mayeul sgc Mayeul sgc - 22 days ago 3
MySQL Question

Count pairs A-B and B-A with a third argument

I have a little table with 3 column :

+---------------------------+
| col 1 | col2 | col 3 |
+---------------------------+
| A | B | 10 |
| A | B | 20 |
| B | A | 20 |
| B | C | 10 |
| B | C | 20 |
| A | C | 10 |
| C | A | 10 |


What I want to to is to count the number of different value of the column 3 each couple (col 1, col 2) gets.
My problem is I can't get to handle the doublon as (A,B) is the same as (B,A).

Here is the result I try to get :

+------------------------+
| col 1 | col 2 | count()|
+------------------------+
| A | B | 2 | // OR BA : 2
| B | C | 2 | // OR CB : 2
| A | C | 1 | // OR CA : 1

Answer

I think you want count(distinct) and least() and greatest():

select least(col1, col2) as col1, greatest(col1, col2) as col2
       count(distinct col3) as cnt
from t
group by least(col1, col2), greatest(col1, col2);
Comments