sql server - SQL group by as subquery -

i have table , 1 column contain id number number + letters. know jow separate , create (new) column group new column:

 select  a.column1 ,          b.column1,           (left(substring(b.column1, patindex('%[0-9.-]%', b.column1), 10),                patindex('%[^0-9.-]%', substring(b.column1, patindex('%[0-9.-]%', b.column1), 10) + 'x') -1)) name2         table_b b                        left outer join table_a on b.column123 = a.column123        a.column1='value' group b.column1,  a.column1 

so report looks like:

    name1   name2  name2     value1  1      1     value   2      2     value3  3      1     value   4      4     value   27xz   27    value    27zx    27 

how group name2?

the easiest way use cte or subquery:

 cte (        select a.column1, b.column1,                 (left(substring(b.column1, patindex('%[0-9.-]%', b.column1), 10),                patindex('%[^0-9.-]%', substring(b.column1, patindex('%[0-9.-]%', b.column1), 10) + 'x') -1)) new_column        table_b b left outer join             table_a             on b.column123 = a.column123        a.column1 = 'value'       ) select new_column, count(*) cte group new_column;