试一试：

select substring(procedure,1,3) as pro, count(company_id) from your_table
group by substring(procedure, 1,3);

使用 Like 操作员输入 CASE 实现此目的的表达式：

DECLARE @T TABLE(
ID INT IDENTITY (1,1),
Company_ID INT ,
[Procedure] VARCHAR(10))

INSERT INTO @T VALUES(1,'TST1')
INSERT INTO @T VALUES(2,'TST1')
INSERT INTO @T VALUES(3,'CHK1')
INSERT INTO @T VALUES(4,'CHK2')
INSERT INTO @T VALUES(5,'TST3')
INSERT INTO @T VALUES(6,'TST3')

主要查询：

SELECT CASE WHEN [PROCEDURE] LIKE 'TST%'THEN'TST*'
            WHEN [PROCEDURE] LIKE 'CHK%'THEN'CHK*' END AS [PROCEDURE]
        ,COUNT(COMPANY_ID) AS [COUNT] FROM @T
GROUP BY CASE WHEN [PROCEDURE] LIKE 'TST%'THEN 'TST*'
              WHEN [PROCEDURE] LIKE 'CHK%'THEN 'CHK*' END