SQL Server: how to group by substring

Question

I have the following stored procedure to retrieve data from a table. The table has a column "region" that contains a value of the type "APAC: China", etc. For which I use the substring function to delete: and everything after it.

It is shown below that it lists all the entries separately, rather than grouping them by substring. So I have, for example. several items with the "APAC" area, not just one with all of them that appear 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 anyone help me with this?

Thanks for any help, Tim.

Answer 1

Your group by will not know if you are referencing the base column or the output of the function code (it will read the base column), so you need to repeat the code in 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