Handle Group by different way in postgres

This query getting result like below

select date_part('hour', ts.a_start_time) AS hour,count(*) as c,
        count(*) FILTER (WHERE EXTRACT(dow FROM ts.a_start_time) = 0) AS s,
        count(*) FILTER (WHERE EXTRACT(dow FROM ts.a_start_time) = 1) AS m,
        count(*) FILTER (WHERE EXTRACT(dow FROM ts.a_start_time) = 2) AS t,
        count(*) FILTER (WHERE EXTRACT(dow FROM ts.a_start_time) = 3) AS w,
        count(*) FILTER (WHERE EXTRACT(dow FROM ts.a_start_time) = 4) AS th,
        count(*) FILTER (WHERE EXTRACT(dow FROM ts.a_start_time) = 5) AS f,
        count(*) FILTER (WHERE EXTRACT(dow FROM ts.a_start_time) = 6) AS sa
        from flight_schedules as fs 
        inner join resource_mapping as rm on rm.flight_schedules_id = fs.id 
        inner join task_schedule_details as tsd on tsd.id = rm.task_schedule_detail_id 
        inner join task_status as ts on ts.resource_mapping_id = rm.id 
        inner join task_master as tm on tm.id = tsd.task_id 
        inner join delay_code_master as dcm on dcm.id = ts.delay_code_id 
        inner join delay_categorization as dc on dc.id = dcm.delay_category_id
        Where fs.station=81 group by hour order by hour ASC

But i want group by hour like

Answer

you can use case when hour to merge multi hours to time range then group it.

select 
    (case when date_part('hour', ts.a_start_time) <= 6 then '1 to 6'
         when date_part('hour', ts.a_start_time) <= 12 then '6 to 12'
         when date_part('hour', ts.a_start_time) <= 18 then '12 to 18'
         else '18 to 23'
        end 
    )AS hour,
    count(*) as c,
    count(*) FILTER (WHERE EXTRACT(dow FROM ts.a_start_time) = 0) AS s,
    count(*) FILTER (WHERE EXTRACT(dow FROM ts.a_start_time) = 1) AS m,
    count(*) FILTER (WHERE EXTRACT(dow FROM ts.a_start_time) = 2) AS t,
    count(*) FILTER (WHERE EXTRACT(dow FROM ts.a_start_time) = 3) AS w,
    count(*) FILTER (WHERE EXTRACT(dow FROM ts.a_start_time) = 4) AS th,
    count(*) FILTER (WHERE EXTRACT(dow FROM ts.a_start_time) = 5) AS f,
    count(*) FILTER (WHERE EXTRACT(dow FROM ts.a_start_time) = 6) AS sa
from flight_schedules as fs 
inner join resource_mapping as rm on rm.flight_schedules_id = fs.id 
inner join task_schedule_details as tsd on tsd.id = rm.task_schedule_detail_id 
inner join task_status as ts on ts.resource_mapping_id = rm.id 
inner join task_master as tm on tm.id = tsd.task_id 
inner join delay_code_master as dcm on dcm.id = ts.delay_code_id 
inner join delay_categorization as dc on dc.id = dcm.delay_category_id
Where fs.station=81 
group by hour 
order by hour ASC