title: get counts of combinations when countries occur together
body: <p>I have a table like this:</p>
  
  <pre><code>Location 1 | Location 2 | ID (autoIncremented)
  </code></pre>
  
  <p>The location rows are in this sytax:</p>
  
  <pre><code>Country*State*City
  </code></pre>
  
  <p>So I can have rows such as this:</p>
  
  <pre><code>USA*NY*BROOKLYN
  USA*WASHINGTON*SEATTLE
  USA*WASHINGTOM*BELLINGHAM
  CANADA*BC*VANCOUVER
  CANADA*MANITOBA*WINNIPEG
  MEXICO*MEHICO*MEXICOCITY
  </code></pre>
  
  <p>I would like to get a result such as this:</p>
  
  <pre><code>Country 1 | Country 2 | count([count of all the occurrences together])
  </code></pre>
  
  <p>But I am stuck on achieving this . I would like to count the combinations of countries which occur together. I need to extract the country part of the, so I use:</p>
  
  <pre><code>substring_index(location1, '*', 1) as country
  </code></pre>
  
  <p>The closest complete query I have, but is not working proeperly is:</p>
  
  <pre><code>select 
  substring_index(location1, '*', 1) as country1,
  substring_index(location2, '*', 1) as country2
  count(*)
  FROM location_table
  GROUP BY [not sure which to group by]
  </code></pre>