This doesn't make much sense. Are the ranges hardcoded or variable? how are they determined? You can simply do a sum and a case when to handle it, but it's not clear
drop table if exists #t
create table #t (
ID int,
col2 int,
col3 int)
insert into #t values
(1 ,0, 0)
,(2 ,0, 0)
,(3 ,-1, -1)
,(4 ,0 ,-1)
,(5 ,-1, 0)
,(6 ,0 ,0)
,(7 ,-1, 0)
,(8 ,-1, 0)
,(9 ,0 ,-1)
,(10, 0 ,0)
,(11, -1, 0)
,(12, 0 ,0)
select sum(case when ID between 1 and 4 and (Col2 = -1 or Col3 = -1) then 1 else 0 end) ID1_4,
sum(case when ID between 5 and 8 and (Col2 = -1 or Col3 = -1) then 1 else 0 end) ID5_8,
sum(case when ID between 9 and 12 and (Col2 = -1 or Col3 = -1) then 1 else 0 end) ID9_12
from #t