sql server - How to get most frequent values in SQL where value like a variable? -

i have sql table, in store application logs. have column errors, in store values example

+------+--------+----------------------------------------------+ |  id  |  name  |  error                                       | +------+--------+----------------------------------------------+   |  1   |  john  |  flushing folder error on folderid 456       | |  2   |  paul  |  flushing folder error on folderid 440       | |  3   |  gary  |  error connection has timed out on source 320| |  4   |  ade   |  error connection has timed out on source 220| |  5   |  fred  |  error connection has timed out on source 821| |  6   |  bob   |  reading errors occured on folder 400        | |  7   |  ade   |  error connection has timed out on source 320| |  8   |  fred  |  error connection has timed out on source 320| |  9   |  bob   |  reading errors occured on folder 402        | |  10  |  ade   |  error connection has timed out on source 320| |  11  |  fred  |  error connection has timed out on source 320| |  12  |  bob   |  reading errors occured on folder 400        | |  13  |  paul  |  flushing folder error on folderid 100       | +------+--------+----------------------------------------------+ 

the result :

+-------------------------------------------+------------+ |  error                               |  occurence | +-------------------------------------------+------------+  | error connection has timed out on source  | 7          | | flushing folder error on folderid         | 3          | |reading errors occured on folder           | 3          | +-------------------------------------------+------------+ 

is there anyway in sql? errors variables , don't have exhaustive list of possible errors.

thanks!

remove last digit part , count.

query

select left(error, len(error) - 4) [error like],  count(left(error, len(error) - 4)) [occurence] tbl_error group left(error, len(error) - 4); 

or can sub-query also.

query

select t.[error like], count(t.[error like]) [occurence] from(     select left(error, len(error) - 4) [error like]     tbl_error )t group t.[error like]; 

if not aware last digit part, then

query

select t.[error like], count(t.[error like]) [occurence] from(     select left(error, len(error) - charindex(' ', reverse(error), 1)) [error like]     tbl_error )t group t.[error like] order count(t.[error like]) desc, t.[error like]; 

result

+--------------------------------------------+-----------+ | error                                 | occurence |  +--------------------------------------------+-----------+ | error connection has timed out on source   | 7         |  | flushing folder error on folderid          | 3         |  | reading errors occured on folder           | 3         |  +--------------------------------------------+-----------+ 

find demo here