Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: How to group by substring

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.

like image 327
user2571510 Avatar asked Dec 02 '13 13:12

user2571510


People also ask

Can we use substring in Group by clause?

Yes I agree with you. But if substr(country,0,20) is same for 2 country , but the actual value of country is different, in that case, we can't display both country value in a group by output.

How do I group concatenate strings in SQL Server?

MySQL | Group_CONCAT() Function. The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value. Otherwise, it returns NULL.

Can I use Concat with group by?

To concatenate strings in MySQL with GROUP BY, you need to use GROUP_CONCAT() with a SEPARATOR parameter which may be comma(') or space (' ') etc.


1 Answers

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
like image 137
dav1dsm1th Avatar answered Oct 12 '22 07:10

dav1dsm1th