Average between select results

Question

I need to calculate the average repair time for each application. Knowing that I have in hand the repair code, application code and time for each repair, what would be the best way to perform this calculation in SQL?

SELECT 
    OS.TAG AS OS,
    APLIC.TAG AS APLICACAO,
    CONVERT(DECIMAL(10,2), ROUND(DATEDIFF(SECOND, OS.MAQPAR, OS.MAQFUN)/3600.0, 2)) AS TEMPO_REPARO
FROM [ENGEMAN].[APLIC] AS APLIC
    JOIN [ENGEMAN].[ORDSERV] AS OS ON APLIC.CODAPL = OS.CODAPL
WHERE OS.STATORD != 'C'
    AND OS.MAQPAR IS NOT NULL 
    AND OS.CODFIL = 2 

NOTE: SQL SERVER 2012!

Answer 1

   SELECT DISTINCT 
        OS.TAG AS OS,
        APLIC.TAG AS APLICACAO,
        AVG(CONVERT(DECIMAL(10,2), ROUND(DATEDIFF(SECOND, OS.MAQPAR, OS.MAQFUN)/3600.0, 2)) OVER(PARTITION BY OS.TAG,APLIC.TAG ) AS MEDIA_TEMPO_REPARO
    FROM [ENGEMAN].[APLIC] AS APLIC
        JOIN [ENGEMAN].[ORDSERV] AS OS ON APLIC.CODAPL = OS.CODAPL
    WHERE OS.STATORD != 'C'
        AND OS.MAQPAR IS NOT NULL 
        AND OS.CODFIL = 2 

Answer 2

You can use the function AVG in the column to calculate the average:

SELECT 
    avg(CONVERT(DECIMAL(10,2), ROUND(DATEDIFF(SECOND, OS.MAQPAR, OS.MAQFUN)/3600.0, 2))) AS TEMPO_REPARO
FROM [ENGEMAN].[APLIC] AS APLIC
    JOIN [ENGEMAN].[ORDSERV] AS OS ON APLIC.CODAPL = OS.CODAPL
WHERE OS.STATORD != 'C'
    AND OS.MAQPAR IS NOT NULL 
    AND OS.CODFIL = 2