[dbo].[ProductTT] (ID int , Product Varchar(50) , Time Int)

1   XX   0030
2   UY   0354
3   YY   0517
4   ZZ   0712
5   WW   0415
6   GG   1112
7   MM   1030
8   HH   0913

With CTE
AS (select ID,
       product,
       [time],
       Case
         When left(time,2)>=00 and left(time,2)< 03  then  'group1'
         when left(time,2)>=03 and left(time,2)< 06  then  'group2'
         when left(time,2)>=06 and left(time,2)< 09  then  'group3'
         when left(time,2)>=09 and left(time,2)<=12  then  'group4' End AS groupID
from [dbo].[ProductTT]

)
 select groupid,count(*) as recordcount
 from cte
 group by groupid