title: MSSQL: GROUP BY and Count
body: <p>I have 3 tables that contain info about users. I would like to find how many of each type of item in each bucket a person has.</p>
  <p>I'm not grasping why this wouldn't work. Does table join order matter or something I not aware of?</p>
  <p>A sample of the tables:</p>
  <p>PERSONS</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>NAME</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>John</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Jane</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>BUCKETS</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>LABEL</th>
  <th>PERSONID</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Random</td>
  <td>1</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Vacation</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>THINGS</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>BUCKETID</th>
  <th>TYPE</th>
  <th>VALUE</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>Image</td>
  <td>abc12</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  <td>Image</td>
  <td>abc13</td>
  </tr>
  <tr>
  <td>3</td>
  <td>1</td>
  <td>Video</td>
  <td>abc34</td>
  </tr>
  <tr>
  <td>4</td>
  <td>1</td>
  <td>Image</td>
  <td>def12</td>
  </tr>
  <tr>
  <td>5</td>
  <td>1</td>
  <td>Video</td>
  <td>def34</td>
  </tr>
  </tbody>
  </table>
  </div>
  <pre class="lang-sql prettyprint-override"><code>SELECT P.NAME, B.LABEL, T.TYPE, COUNT(T.TYPE)
  FROM PERSONS P
    LEFT JOIN BUCKETS B ON
      B.PERSONID = P.ID
    LEFT JOIN THING T ON
      T.BUCKETID = B.ID
  GROUP BY P.NAME, B.LABEL, T.TYPE
  </code></pre>
  <p>I expect it to return:</p>
  <pre><code>John, Random, Images, 3
  John, Random, Videos, 2
  </code></pre>
  <p>But it returns:</p>
  <pre><code>John, Random, Images, 5
  John, Random, Videos, 5
  </code></pre>
  <p>I have tried <code>COUNT(*)</code> which results in the same and <code>COUNT(DISTINCT T.TYPE)</code> which of course returns 1 as the count.</p>
  <p>This works perfectly in MySQL. Fiddle here: <a href="https://www.db-fiddle.com/f/vcb3wiPMSAFBXrWbgYxuMH/8" rel="nofollow noreferrer">https://www.db-fiddle.com/f/vcb3wiPMSAFBXrWbgYxuMH/8</a></p>
  <p>MSSQL is a different beast all together.</p>