Averaging the Results of Query in a Separate Column

Question

I have the following query and get the following results, what I want is a column the right which averages only then values. In this example each would show 25.8:

SELECT 
    CASE
        WHEN LEFT(WORKORDER_LOT_ID,1) = 'A' 
            THEN '20' + RIGHT(WORKORDER_LOT_ID,2)
        WHEN LEFT(WORKORDER_LOT_ID,1) = 'J' 
            THEN '30' + RIGHT(WORKORDER_LOT_ID,2)
        ELSE ''
    END AS Serial_Number,
    SUM(HOURS_WORKED) AS Total_Hours
FROM
    LABOR_TICKET
WHERE  
    WORKORDER_BASE_ID LIKE '23765%'
    AND WORKORDER_LOT_ID IN ('A43', 'A44', 'A45', 'A46', 'A47', 'A48', 
                             'J43', 'J44', 'J45', 'J46', 'J47', 'J48')
GROUP BY 
    CASE
        WHEN LEFT(WORKORDER_LOT_ID,1) = 'A' THEN '20' + RIGHT(WORKORDER_LOT_ID,2)
        WHEN LEFT(WORKORDER_LOT_ID,1) = 'J' THEN '30' + RIGHT(WORKORDER_LOT_ID,2)
        ELSE ''
    END;

Result of query:

Serial_Number Total_Hours
---------------------------
2043       43.97
2044       31.99
2045       42.88
2046       29.80
2047       10.97
2048       18.02
3043       8.13
3044       38.53
3045       25.73
3046       31.99
3047       16.79
3048       11.20

I've tried playing around with subqueries but I didn't get the results I wanted.

Answer 1

You can use a window function AVG() OVER (). Note that window functions are always calculated after normal aggregation, so you can do AVG(SUM(...)) OVER (...)

You can also put the complex CASE into an APPLY so you don't need to repeat it

SELECT
  v.Serial_Number,
  SUM(l.HOURS_WORKED) AS Total_Hours,
  AVG(SUM(l.HOURS_WORKED)) OVER () AS Average_Hours
FROM LABOR_TICKET l
CROSS APPLY ((
    CASE
    WHEN LEFT(l.WORKORDER_LOT_ID,1) = 'A' THEN '20' + RIGHT(l.WORKORDER_LOT_ID,2)
    WHEN LEFT(l.WORKORDER_LOT_ID,1) = 'J' THEN '30' + RIGHT(l.WORKORDER_LOT_ID,2)
    ELSE ''
    END
)) AS v(Serial_Number)
WHERE l.WORKORDER_BASE_ID LIKE '23765%'
  AND l.WORKORDER_LOT_ID IN
   ('A43','A44','A45','A46','A47','A48','J43','J44','J45','J46','J47','J48')
GROUP BY v.Serial_Number;

If you have integers then you need to convert to decimal first

  AVG(SUM(l.HOURS_WORKED) * 1.0) OVER () AS Average_Hours