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

Question

Next, I am developing a query in SQL and the purpose of this query is to Count the records that reached the value below 99,7%(As I have the data for this already calculated and transformed in seconds) but it is not working, and just 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 bring in the count that which is less than 366959808(Or 99.7%)

Grateful

Answer 1

The cause of the error is that an aggregation function (SUM) was used within another aggregation function (COUNT).

Evaluate:

-- 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;