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

Posted by TomGasson on Stack Overflow See other posts from Stack Overflow or by TomGasson
Published on 2010-12-22T05:27:28Z Indexed on 2010/12/22 5:54 UTC
Read the original article Hit count: 227

Filed under:

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.

© Stack Overflow or respective owner

Related posts about mysql-query