How to do a query in SQL that brings a count of some results

Question

How to do a query in SQL that brings a count of some results

Navigation

#1 by (0 votes)

1

Next, I'm developing a query in SQL and the purpose of this query is to count the records that reached the value below 99.7% (I have the data for that already calculated and transformed in seconds) but it is not working, and has returned an error message in SQL Server.

Below is the QUERY code

SELECT  COUNT(CASE WHEN(
    SUM(CASE
        WHEN LEN([TEMPO INDISPONÍVEL]) = 8 AND [INICIO] BETWEEN '2018-08-01' AND '2018-08-31 23:59:59'
                 THEN (SUBSTRING([TEMPO INDISPONÍVEL], 1, 2) * 3600) 
            + (SUBSTRING([TEMPO INDISPONÍVEL], 4, 2) * 60) + (SUBSTRING([TEMPO INDISPONÍVEL], 7, 2)) 

                WHEN LEN([TEMPO INDISPONÍVEL]) = 9 AND [INICIO] BETWEEN '2018-08-01' AND '2018-08-31 23:59:59' 
            THEN (SUBSTRING([TEMPO INDISPONÍVEL], 1, 3) * 3600) 
            + (SUBSTRING([TEMPO INDISPONÍVEL], 5, 2) * 60) + (SUBSTRING([TEMPO INDISPONÍVEL], 8, 2)) 
        END)) < 366959808  THEN 1 ELSE NULL END) AS Total_Indisponibildiade_Inferior
 FROM [REPORT].[dbo].[report_sirea]
  WHERE [OPERADORA] = 'ALP' AND [GRUPO] = 'OPERADORA'

The error that returns is as follows

  Msg 130, Level 15, State 1, Line 3
  Cannot perform an aggregate function on an expression containing an 
  aggregate or a subquery.

What could be done to fix this and it only brings in the count that which is less than 366959808 (Or 99.7%)

Thankful

sql sql-server

asked by anonymous 22.09.2018 / 23:22

1 answer

Error connecting to firebase Google Maps does not work (After publishing in google play)

score 0 · Answer 1

The cause of the error is that an aggregate function (SUM) has been used inside another aggregation function (COUNT).

Rate it:

-- código #1 v3
;with Soma_Indisp as (
SELECT sum (case when len ([TEMPO INDISPONÍVEL]) = 8
                      then datediff (second, 0, cast ([TEMPO INDISPONÍVEL] as time(0)))
                 when len ([TEMPO INDISPONÍVEL]) = 9
                      then (cast (substring ([TEMPO INDISPONÍVEL], 1, 3) as int) * 3600) +
                           (cast (substring ([TEMPO INDISPONÍVEL], 5, 2) as int) * 60) +
                           (cast (substring ([TEMPO INDISPONÍVEL], 7, 2) as int) 
                 else (1/0) end
           ) as Somado
  from REPORT.dbo.report_sirea
  where OPERADORA = 'ALP' and GRUPO = 'OPERADORA' 
        and INICIO >= '20180801' and INICIO < '20180901'
)
SELECT count(*) as Total_Indisponibilidade_Inferior
  from Soma_Indisp
  where Somado < 366959808;