merge values in select

I have next part of query:

SELECT types, count
FROM ...

Result is next:

types        count
soft         3
lite soft    2
middle soft  7
hard soft    2
other        5

what I need is to merge results soft, lite soft and other as other. to get next table:

types        count
middle soft  7
hard soft    2
other        9

Answer

You can use a case expression`:

select (case when type in ('middle soft', 'hard soft') then type else 'other' end) as type,
       sum(count)
from t
group by type;

If you want the result in a particular order — say with other at the end, then a lateral join helps:

select v.type, sum(count)
from t cross join lateral
     (values (case when type in ('middle soft', 'hard soft') then type else 'other' end)
     ) v(type)
group by v.type
order by (v.type = 'other') asc,  -- put it last
         count(*) desc;