group by with case

group by with case

declare @test table (
	color_id integer, 
	color_name varchar(50)
);

insert into @test (color_id, color_name) values (1,'red');
insert into @test (color_id, color_name) values (1,'red');
insert into @test (color_id, color_name) values (1,'red');
insert into @test (color_id, color_name) values (2,'blue');
insert into @test (color_id, color_name) values (3,'yellow');
insert into @test (color_id, color_name) values (3,'yellow');
insert into @test (color_id, color_name) values (4,'orange');
insert into @test (color_id, color_name) values (1,'red');
insert into @test (color_id, color_name) values (1,'red');
insert into @test (color_id, color_name) values (1,'red');



select color_name , sum(cnt)
from (
select 
case when color_name = 'red' then 'red' else 'colors other than red' end as color_name, 
count(*) as cnt
from @test
group by color_name) as q
group by color_name;