Average value by group-CodePudding

Let's say I have this query below which selects all the sectors and gets the value when the date falls on any of those 3 dates specified in the WHERE clause.

SELECT 
Sector
,Val
FROM [Consumption] upc
WHERE upc.PeriodStart = '2020-12-07 17:00:00' 
    OR upc.PeriodStart = '2021-01-07 17:30:00' 
    OR upc.PeriodStart = '2021-02-10 18:00:00'

Current Result:

Sector        Val
Retail        53.4
Retail        58.1
Retail        60.5
Commercial    58.2
Commercial    60.6
Commercial    53.5

How would I go about getting the average between the values grouped by the sectors multiplied by 2 so that the result will look like below?

Expected Result:

Sector        Val       AvTot
Retail        53.4      114.67
Retail        60.5      114.67
Retail        58.1      114.67
Commercial    58.2      114.87
Commercial    60.6      114.87
Commercial    53.5      114.87

CodePudding user response：

A windowed AVG() returns the expected results:

SELECT Sector, Val, AVG(Val * 2) OVER (PARTITION BY Sector) AS AvgTotal
FROM (VALUES
   ('Retail',        53.4),
   ('Commercial',    58.2),
   ('Retail',        60.5),
   ('Commercial',    60.6),
   ('Commercial',    53.5),
   ('Retail',        58.1)
) [Consumption] (Sector, Val)
ORDER BY Sector

Result (without rounding and/or casting):

Sector     Val  AvgTotal
--------------------------
Commercial 58.2 114.866666
Commercial 60.6 114.866666
Commercial 53.5 114.866666
Retail     60.5 114.666666
Retail     58.1 114.666666
Retail     53.4 114.666666