title: Count based on left characters?
body: <p>I have a table that looks like this:</p>
  
  <pre><code>ID       Location
  1        AAA123
  1        AAA133
  1        AAA832
  1        BBB929
  1        BBB420
  </code></pre>
  
  <p>How can I count and group by the first 3 chars so that the result looks like this:</p>
  
  <pre><code>ID      AAA_Count      BBB_count
  1       3               2
  </code></pre>
  
  <p>I tried something like this:</p>
  
  <pre><code>select [ID], Location,
  case when left(location, 3) = 'AAA' then count(location) end as 'AAA',
  case when left(location, 3) = 'BBB' then count(location) end as 'BBB',
  from Table
  group by [ID], left(location, 3)
  </code></pre>
  
  <p>Any help is appreciated.</p>
  
  <p>Thanks.</p>