SQL Server: How to group by substring

Question

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.

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.

My stored procedure:

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

My result:

<ranks>
  <regions>
    <region>APAC</region>
    <groupCount>1</groupCount>
  </regions>
  <regions>
    <region>EMEA</region>
    <groupCount>1</groupCount>
  </regions>
  <regions>
    <region>APAC</region>
    <groupCount>1</groupCount>
  </regions>
  // ...
</ranks>

Expected result:

<ranks>
  <regions>
    <region>APAC</region>
    <groupCount>2</groupCount>
  </regions>
  <regions>
    <region>EMEA</region>
    <groupCount>1</groupCount>
  </regions>
  // ...
</ranks>

Can anyhone here help me with this ?

Thanks for any help, Tim.

Answer 1

Your group by would not know whether you are referring to the underlying column, or the output of your function code (it would assume the underlying column), so you need to repeat the code into the group by:-

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    SUBSTRING(region, 1, CHARINDEX(':', region) - 1)
    ORDER BY    groupCount desc, region
    FOR XML PATH('regions'), ELEMENTS, TYPE, ROOT('ranks')
END