title: Grouping related records in many to many table in MySQL
body: <p>I Have a table 'C' in MySQL db with the following structure:</p>
  
  <p><em>Object1Id (int)</em> - <strong>PK</strong> and has one to many relation to table 'A',</p>
  
  <p><em>Object2Id (int)</em> - <strong>PK</strong> and has one to many relation to table 'A',</p>
  
  <p><em>OccuranceId (int)</em> - <strong>FK</strong> and has one to many relation to table 'B'.</p>
  
  <p>Basically table 'C' has many to many relation with table 'A' and one to many relation with table 'B'.</p>
  
  <p>The data:</p>
  
  <pre><code>1, 3, 1
  1, 3, 39
  3, 1, 402
  3, 1, 27
  1, 3, 40
  3, 1, 12
  </code></pre>
  
  <p>I try to group the records with the query:</p>
  
  <pre><code>SELECT CASE WHEN least(Object1Id, Object2Id) = Object1Id THEN Object1Id ELSE Object2Id END AS Object1Id, 
         CASE WHEN Greatest(Object1Id, Object2Id) = Object2Id THEN Object2Id ELSE Object1Id END AS Object2Id, 
         count(OccuranceId) AS OccuranceCount
  FROM 'C'
  GROUP BY Object1Id, Object2Id
  </code></pre>
  
  <p>The result I get back from the query are:</p>
  
  <p>1, 3, 3 - for object1Id = 1 and Object2Id = 3</p>
  
  <p>1, 3, 3 - for Object1Id = 3 and Object2Id = 1</p>
  
  <p>I would like the result to sum up as : 1, 3, 6</p>
  
  <p>How can I produce the desired result? </p>
  
  <p>Thanks.</p>