how to count the number of records group by remark in sql server

December 28, 2023

How to count the records of each remark. Each remark have 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

>Solution :

Filter the real remark out of the remark column, and group on that:

select  the_real_remark
,       count(*)
from    (
        select  left(remark, charindex('-', remark) - 1) as
                    the_real_remark
        from    YourTable
        ) as SubQueryAlias
group by
        the_real_remark