group each period per month

Question

I’m doing this consultation, but I’m not able to group each period per month, the previous year.

For each month of the year preceding the current year, return the amount of parked cars per period (from 00:00 to 07:59, 08:00 to 15:59 and 16:00 to 23:59).

Consultation:

select 
  ent_datahora_entrada as mes, veiculos as periodo  , count(*) 
from (
      select  
      
       to_char(ent_datahora_entrada, 'dd/mm/yyyy hh24:mi') ent_datahora_entrada 
        
      , case when to_char(ent_datahora_entrada, 'hh24:mi')>='00:00'
              and to_char(ent_datahora_entrada, 'hh24:mi')<='07:59' then '0 as 8'
             when to_char(ent_datahora_entrada, 'hh24:mi')>='08:00'
              and to_char(ent_datahora_entrada, 'hh24:mi')<='15:59' then '8 as 16'
             when to_char(ent_datahora_entrada, 'hh24:mi')>='16:00'
              and to_char(ent_datahora_entrada, 'hh24:mi')<='23:59' then '16 as 0'
             
        end veiculos
      from entradas_saidas
     ) 
group by ent_datahora_entrada, veiculos ;

It’s coming out like this:

12/01/0020 09:26  |8 as 16 |1
14/01/0020 11:26  |8 as 16 |1
19/01/0020 16:26  |16 as 0 |1
28/01/0020 01:26  |0 as 8  |1
17/02/0020 21:26  |16 as 0 |1
01/03/0020 10:26  |8 as 16 |1

What I need is to group all the dates of the month. Example : 01/01/19 until 01/02/19 for every month.

 8 as 16   |  5
16 as 00   | 10
00 as 8    | 7

Answer 1

You have to transform the date field ent_datahora_entrada in such a way that it has the desired format.

In this way:

select 
  ent_datahora_entrada as mes, veiculos as periodo  , count(*) 
from (
      select          
        to_char(ent_datahora_entrada, 'mm/yyyy') ent_datahora_entrada,          
        case when to_char(ent_datahora_entrada, 'hh24:mi')>='00:00'
              and to_char(ent_datahora_entrada, 'hh24:mi')<='07:59' then '0 as 8'
             when to_char(ent_datahora_entrada, 'hh24:mi')>='08:00'
              and to_char(ent_datahora_entrada, 'hh24:mi')<='15:59' then '8 as 16'
             when to_char(ent_datahora_entrada, 'hh24:mi')>='16:00'
              and to_char(ent_datahora_entrada, 'hh24:mi')<='23:59' then '16 as 0'                 
        end veiculos
      from entradas_saidas
) 
group by ent_datahora_entrada, veiculos;