Mayeul sgc - 7 months ago 34

MySQL Question

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);
```

Source (Stackoverflow)