SQL CASE statement that's not a waterfall grouping

Question

I have 3 categories:

categories   number_units
CAT_A        10 
CAT_B        20
CAT_C        30

If I do a GROUP BY for column categories I will get the 3 categories seen above. But if I want to do a case statement like:

SELECT CASE WHEN categories LIKE 'CAT%' ...

(to show the sum of all those that contain CAT it will exclude the groupings like CAT_A, _B, _C)

How can I write a query that will get the following result:

categories   sum
CAT          60
CAT_A        10
CAT_B        20
CAT_C        30

Answer 1

Many databases support the standard grouping sets functionality:

select coalesce(category, 'CAT'), sum(number_of_units)
from t
group by grouping sets ((), category);

Answer 2

I think with rollup is your bet option. (In this scenario with cube would also return same result)

 create table cat(categories varchar(50),   number_units int);
 insert into cat values('CAT_A',        10);
 insert into cat values('CAT_B',        20);
 insert into cat values('CAT_C',        30);

Query:

 select Coalesce(categories,'CAT')categories   ,sum(number_units) [sum] 
 from cat 
 group by categories with rollup
 order by categories

 GO

Output:

categories	sum
CAT	60
CAT_A	10
CAT_B	20
CAT_C	30

db<fiddle here