SQL Group with time

Sample table

id   dept   ctime
100  A      09:15:12.0000000
200  B      08:00:00.0000000
300  A      09:00:00.0000000
400  B      08:50:00.0000000

expect to see

countid   dept  xtime
2          A    08:00:00 08:50:00
2          B    09:00:00 09:15:12

My query

SELECT count(id) countid,  dept, 
        DATEADD(MI, -(DATEPART(MI, ctime) % 5), cTime) xtime
FROM table1 
GROUP BY  dept, DATEADD(MI, -(DATEPART(MI, ctime) % 5), cTime)

Output. But not sure why not group by hours

countid dept    xtime
1        B      08:00:00.0000000
1        B      08:50:00.0000000
1        A      09:00:00.0000000
1        A      09:15:12.0000000

Answer

Based on this comment, it seems you just want to aggregate to the hours:

Try to group by each hours like between 8:00:00 to 8:59:00 and 9:00:00 to 9:59:00 and by dept.

If so, why not just use DATEPART with MIN and MAX?

SELECT COUNT(ID) AS CountID,
       Dept,
       MIN(ctime) AS MinTime,
       Max(ctrime) AS MaxTime
FROM dbo.YourTable
GROUP BY Dept,
         DATEPART(HOUR, ctime);