Count the number of records group by remark

Question

How to count the records of each remark? Each remark has a different number inside the remark column value. I need to group the records based on Remark content but not with cid###. need to group by remark but each remark has some dynamic variable of cid.excluding cid number need to group it

Example - Remarks:

Remark1-cid11
Remark1-cid22
Remark1-cid33

Count of Group1 should be 3

Sample data

Remark                Id    CreatedOn                        ProcessName
------------------------------------------------------------------------
Remark1-cid22          1    2023-12-27 18:02:54.4102746      P1
Remark2-cid23          2    2023-12-27 18:02:54.4102746      P1
Remark3-cid24          3    2023-12-27 18:02:54.4102746      P1
Remark4-cid25          4    2023-12-27 18:02:54.4102746      P1
Remark1-cid26          5    2023-12-27 18:02:54.4102746      P1
Remark2-cid27          6    2023-12-27 18:02:54.4102746      P1
Remark3-cid28          7    2023-12-27 18:02:54.4102746      P1
Remark4-cid29          8    2023-12-27 18:02:54.4102746      P1

Desired output

CreatedOn  Remark   Count   Group 
-----------------------------------
2023-12-27 Remark1   2      Group1
2023-12-27 Remark2   2      Group2
2023-12-27 Remark3   2      Group3
2023-12-27 Remark4   2      Group4 

Query I tried

select 
    Remark, Count (Id) 
from 
    IPocumentRequests
group by 
    Remark
having 
    Remark like '%Remark1%' 
    or Remark like '%Remark2%' 
    or Remark like '%Remark3%'  
    or Remark like '%Remark4%'

Output

Remark        (No column Name)
------------------------------
Remark1 -cid11       1
Remark2 -cid22       1
Remark3-cid33        1
Remark4-cid44        1
Remark1-cid55        1
Remark2-cid66        1
Remark3-cid77        1
Remark4-cid88        1

Answer 1

Check this solution, I used Common Table Expression, it is faster way to work with big data.

WITH aa AS (
  SELECT 
    CONVERT(DATE, created_on) AS created_on,
    SUBSTRING(Remark, 1, CHARINDEX('-', Remark) - 1) AS Remark_modified,
    CASE
      WHEN Remark LIKE '%Remark1%' THEN 'group1'
      WHEN Remark LIKE '%Remark2%' THEN 'group2'
      WHEN Remark LIKE '%Remark3%' THEN 'group3'
      WHEN Remark LIKE '%Remark4%' THEN 'group4'
      ELSE Remark
    END AS Group_num
  FROM 
    your_table_name
)

SELECT 
  created_on,
  Remark_modified,
  Group_num,
  COUNT(*) AS count
FROM 
  aa
GROUP BY 
  created_on,
  Remark_modified,
  Group_num;