I am attempting to run this SELECT
statement, but I keep getting error -
Msg 130, Level 15, State 1, Line 9
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
This is my syntax, how should this be re-written in order to succesfully execute? I also add here make-up DDL to better illustrate question.
Declare @info TABLE (region varchar(50), p1 float, p2 float, p3 float, p4 float, p5 float, necombs float, d1 datetime)
INSERT INTO @info (region, p1, p2, p3, p4, p5, necombs, d1) VALUES
('west', 1.11, 2.22, 3.33, 4.44, 5.11, 22.00, '01/01/2015 08:00:00.000')
,('east', 2.11, 3.22, 3.33, 1.44, 2.11, 12.00, '01/03/2016 08:00:00.000')
,('north', 1.11, 2.22, 3.33, 4.44, 5.11, 22.00, '01/05/2015 08:00:00.000')
Select
region
,SUM(CASE WHEN YEAR(d1) = '2015' THEN ISNULL(p1, 0)+ISNULL(p2, 0)+ISNULL(p3, 0)+ISNULL(p4, 0)+ISNULL(p5, 0)/NULLIF(SUM(ISNULL(necombs, 0)), 0) ELSE 0 END) [Answer]
FROM @info
Group By Region