How to combine some similar pattern data in one column of mysql, to get the count?

I have a table like below

From this table, I want to get the counts of SearchParams, which I tried like this:

But I want all the data which is matching the pattern “TVE-xxxx” should be combined as “TVE” and the count.

Please help.

Answer

Use a CASE statement to determine the value on which you group by:

select 
  case when searchParam like 'TVE-%' then 'TVE' else searchParam end groupParam,
  count(*) counter
from careinsight.testtable
group by groupParam