title: SQL Server: How to group by substring
body: <p>I have the following stored procedure to fetch data from a table. 
  The table has a column "region" that contains value like "APAC: China" etc. for which I am using the substring function in order to remove the : and everything after it. </p>
  
  <p>The below works except that it lists all records separately instead of grouping them by my substring. So I have e.g. several items with region "APAC" instead of just one with all of them appearing below.</p>
  
  <p><strong>My stored procedure:</strong></p>
  
  <pre><code>CREATE PROCEDURE [dbo].[CountRegions]
  AS
  BEGIN
      SET NOCOUNT ON;
      SELECT      SUBSTRING(region, 1, CHARINDEX(':', region) - 1) AS region,
                  COUNT(*) AS groupCount,
      FROM        SOPR_LogRequests
      WHERE       logStatus = 'active'
      GROUP BY    region
      ORDER BY    groupCount desc, region
      FOR XML PATH('regions'), ELEMENTS, TYPE, ROOT('ranks')
  END
  </code></pre>
  
  <p><strong>My result:</strong></p>
  
  <pre><code>&lt;ranks&gt;
    &lt;regions&gt;
      &lt;region&gt;APAC&lt;/region&gt;
      &lt;groupCount&gt;1&lt;/groupCount&gt;
    &lt;/regions&gt;
    &lt;regions&gt;
      &lt;region&gt;EMEA&lt;/region&gt;
      &lt;groupCount&gt;1&lt;/groupCount&gt;
    &lt;/regions&gt;
    &lt;regions&gt;
      &lt;region&gt;APAC&lt;/region&gt;
      &lt;groupCount&gt;1&lt;/groupCount&gt;
    &lt;/regions&gt;
    // ...
  &lt;/ranks&gt;
  </code></pre>
  
  <p><strong>Expected result:</strong> </p>
  
  <pre><code>&lt;ranks&gt;
    &lt;regions&gt;
      &lt;region&gt;APAC&lt;/region&gt;
      &lt;groupCount&gt;2&lt;/groupCount&gt;
    &lt;/regions&gt;
    &lt;regions&gt;
      &lt;region&gt;EMEA&lt;/region&gt;
      &lt;groupCount&gt;1&lt;/groupCount&gt;
    &lt;/regions&gt;
    // ...
  &lt;/ranks&gt;
  </code></pre>
  
  <p>Can anyhone here help me with this ?</p>
  
  <p>Thanks for any help, Tim.</p>