Group by LIKE of a column value

You can group on some string function to reduce your plan type to the substring you want.

Sql Server example:

SELECT 
    left(plan_type,charindex('_',plan_type)-1) as plan_type
  , COUNT(*)
FROM subscriptions
GROUP by left(plan_type,charindex('_',plan_type)-1) 

You can use case:

SELECT (CASE WHEN plan_type LIKE 'silver%' THEN 'silver'
             WHEN plan_type LIKE 'gold%' THEN 'gold'
             WHEN plan_type LIKE 'platinum%' THEN 'platinum'
        END) as plan_grp, COUNT(*)
FROM subscriptions
GROUP by (CASE WHEN plan_type LIKE 'silver%' THEN 'silver'
               WHEN plan_type LIKE 'gold%' THEN 'gold'
               WHEN plan_type LIKE 'platinum%' THEN 'platinum'
          END);

Some databases allow you to use a column alias in the GROUP BY.