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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With