Get the count of A -> B and B->A without duplicates

I have a table like so:

index|from | to
------------------
  1  | ABC | DEF
  2  | ABC | GHI
  3  | ABC | GHI
  4  | ABC | JKL
  5  | ABC | JKL
  6  | ABC | JKL
  7  | DEF | ABC
  8  | DEF | GHI
  9  | DEF | JKL
 10  | GHI | ABC
 11  | GHI | ABC
 12  | GHI | ABC
 13  | JKL | DEF

And I need to count how the total times between the points (regardless of direction) to get the result:

 A  |  B  | count
-----------------
ABC | DEF | 2
ABC | GHI | 5
ABC开发者_运维知识库 | JKL | 3
DEF | GHI | 1
DEF | JKL | 2

So far I can get:

SELECT `a`.`from` as `A`, `a`.`to` as `B`, (`a`.`count` + `b`.`count`) as `count`
FROM
(SELECT `from`, `to`, count(*) as `count`  
FROM `table` 
GROUP BY 1,2) `a`
LEFT OUTER JOIN
(SELECT `from`,`to`, count(*) as `count`  
FROM `table` 
GROUP BY 1,2) `b`
ON `a`.`from` = `b`.`to` 
AND `a`.`to` = `b`.`from`

But I'm unsure how to remove the A/B swapped duplicates.

select `from` as A, `to` as B, count(*) as `count`
from your_table
group by if (from<to, concat(from,to), concat(to,from));

SELECT 'from' as first, 'to' as second
FROM 'table'
UNION
SELECT 'to' as first, 'from' as second
FROM 'table'

That will give you all of the pairs, forward and back. To eliminate the duplicates you just need a WHERE clause that imposes order on first and second.

SELECT first, second, count(*)
FROM (
SELECT 'from' as first, 'to' as second
FROM 'table'
UNION
SELECT 'to' as first, 'from' as second
FROM 'table'
) x
WHERE first < second
GROUP BY first, second

It's simpler to avoid keywords for your field and table names; you can leave the quotes off.