Convert percentage

Asked

Viewed 31 times

1

I’m having trouble converting %

my code:

select 
 veiculos , qtd , trunc(qtd / sum(qtd) over()* 100, 4 ) PERC
from (
   select 
  ent_datahora_entrada , veiculos  , count(*) qtd
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
      where to_char(ent_datahora_entrada, 'YYYY') = 2019
) 

group by ent_datahora_entrada, veiculos
);

It’s coming out like this:

VEICULOS    | QTD   |PERC
8 as 16     | 3     |1.3043
16 as 0     | 29    |12.6086
8 as 16     | 29    |12.6086

I want you to leave like this:

MES     | VEICULOS  | QTD   |PERC
03/2019 |8 as 16    | 3     |1%
03/2019 |16 as 0    | 29    |12%
08/2019 |8 as 16    | 29    |12%
sql database group-by where count

1 answer

1


Deborah, from what I understand is missing only add the column ent_datahora_input in the first Select and change the second function parameter Trunc to 0:

select
  ent_datahora_entrada,  
  veiculos, 
  qtd, 
  trunc(qtd / sum(qtd) over() * 100, 0) PERC 
from 
(
  select
    ent_datahora_entrada, 
    veiculos, 
    count(*) qtd 
  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 
    where to_char(ent_datahora_entrada, 'YYYY') = 2019 
  )
  group by ent_datahora_entrada, veiculos 
);

I hope it helps

Browser other questions tagged sql database group-by where count

You are not signed in. Login or sign up in order to post.