Sum PL SQL Lines

Question

Galerinha, I created a code that organizes all the tasks of the company by sector. The objective of this code is to identify the percentage of delayed activities by sector. The result was expected.

Now I have gathered and told the different tasks.

I wanted to know how to calculate the percentage for each Sector. I tried what I remembered, but I’m out of ideas.

It would be the sum total of the columns my 100% And for sectors would be total sector / total columns.

---

I tried something like:

select 


sum(COUNT(DISTINCTTASK_SEQ)),
SUM(SETOR = 'CALDEIRARIA')/(SUM(CONTAGEM)) AS CALDEIRARIA,
SUM(SETOR = 'ELETRICA')/(SUM(CONTAGEM)) AS ELETRICA,
SUM(SETOR = 'INSPECAO')/(SUM(CONTAGEM)) AS INSPECAO,
SUM(SETOR = 'INSTRUMENTACAO')/(SUM(CONTAGEM)) AS INSTRUMENTACAO,
SUM(SETOR = 'MECANICA')/(SUM(CONTAGEM)) AS MECANICA,
SUM(SETOR = 'OPERACAO')/(SUM(CONTAGEM)) AS OPERACAO,
SUM(SETOR = 'OUTROS')/(SUM(CONTAGEM)) AS OUTROS,

from petro_backlog_equipestotal
group by SETOR

Thanks in advance.

Answer 1

The RATIO_TO_REPORT function is native to Oracle, and aims to show the proportion of a given quantity in Relation to Total.

SELECT NOME_SETOR, 
       ROUND( ( (RATIO_TO_REPORT(QTDE) OVER())*100 ) ,2 ) 
  FROM (
          SELECT NOME_SETOR SETOR, COUNT(*) QTDE    
            FROM TAREFAS 
          GROUP BY NOME_SETOR
       )