Get the count of A -> B and B->A without duplicates
- by TomGasson
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.
