Add up the Count results

Asked

Viewed 51 times

0

I am developing a system, and in case I have the following query in SQL (SQL Server), the query is as follows 

USE REPORT 
GO

SELECT TOP 10

  r.[PONTO DE ATENDIMENTO] , 
  r.[CIRCUITO], 
  count (t.co_ocorrencia) as Oscilações  

    FROM [dbo].[report_sirea] r 

        INNER JOIN [dbo].[report_ocorrencia_auxiliar] t ON t.co_ocorrencia = r.[co_ocorrencia] 
        LEFT JOIN [dbo].[planta_circuitos] p ON r.[CIRCUITO] = p.[DESIGNACAO]

         WHERE r.[INICIO] BETWEEN '2019-01-01' AND '2019-01-22 23:59:59' 
         AND r.[OPERADORA] = 'FOR'
         AND r.[FECHAMENTO OCORRÊNCIA] IS NOT NULL AND r.[GITEC] = 'GITEC/FO' 
         AND p.[TIPO_CIRCUITO] = 'REDE 2' 
         AND r.[GRUPO] = 'Operadora' 

    GROUP BY r.[GITEC], r.[CGC], r.[PONTO DE ATENDIMENTO], 
        r.[CIRCUITO],  
        t.co_ocorrencia ORDER BY Oscilações DESC

In case she is bringing the following result

PONTO DE ATENDIMENTO | CIRCUITO       | Oscilações
PAE AAAAA            |LSR-VPN-F-91510 |     12
PAE BBBBB            |PNA-VPN-F-91512 |     12
AG. CCCCC            |PCR-VPN-F-91700 |     7
AG. DDDDD            |CHI-VPN-F-91692 |     4
AG. EEEEE            |JFT-VPN-F-130464|     4
AG. FFFFF            |PDZ-VPN-R-91590 |     4
AG. GGGGG            |BLA-VPN-F-91736 |     3
AG. HHHHH            |RSI-VPN-F-91716 |     3
AG. IIIII            |JFT-VPN-F-130464|     3
AG. CCCCC            |PCR-VPN-F-91700 |     3

In case, the AG. CCCCC repeats twice giving 7 and 3 Oscillations. In case, I need the result of AG Oscillations. CCCCC be added to reach the 10, in case, would have how? Or need to do the coding in the language I’m using (PHP)?

php sql sql-server

1 answer

3


You are missing when grouping this part of the script:

GROUP BY r.[GITEC], r.[CGC], r.[PONTO DE ATENDIMENTO], 
        r.[CIRCUITO],  
        t.co_ocorrencia ORDER BY Oscilações DESC

you are stating that the results should be grouped by the number of occurrences (t.co_occurrence) and I believe you do not need it, so to return the expected result is just remove the’t. 'GROUP BY' occurrence':

GROUP BY r.[GITEC], r.[CGC], r.[PONTO DE ATENDIMENTO], 
            r.[CIRCUITO]
ORDER BY Oscilações DESC

Browser other questions tagged php sql sql-server

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