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
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;