I Want To Group By similar number and get count-CodePudding

Select * from TableName

Field1
2019063207
2019063207_1
2019063207_2
2019063207_3
2019063208
2019063208_1
2019063208_2
2019063209
2019063210

I expect

Number     Count 
2019063207   4
2019063208   3 
2019063209   1  
2019063210   1

i am Using This select ClientCode,PARSENAME(REPLACE(UniqueCode,'_','.'),2) from [Table Name] But Not Working

CodePudding user response：

you can do it like that (result here):

select substring(field1,1,case when charindex('_',field1) = 0 then len(field1) else charindex('_',field1)-1 end ) as code, count(*) 
from t1
group by substring(field1,1,case when charindex('_',field1) = 0 then len(field1) else charindex('_',field1)-1 end )

Or by using CTE (result here)

with codes as (select substring(field1,1,case when charindex('_',field1) = 0 then len(field1) else charindex('_',field1)-1 end ) as code 
from t1)
select code, count(*) 
from codes
group by code