0

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

2 Answers 2

4

The accepted answer doesn't show the 2016 row, which I would expect to be 0 based on the initial SQL in the question.

Here's an alternative that would give the additional 2016 row:

 SELECT i.region,
        DATEPART(YEAR, i.d1) AS YearNumber,
        SUM(rd.Calc) / ISNULL(SUM(ISNULL(necombs, 0)), 0) Answer
 FROM @info i
 CROSS APPLY (VALUES(CASE WHEN DATEPART(YEAR, d1) = '2015' THEN ISNULL(p1, 0) + ISNULL(p2, 0) + ISNULL(p3, 0) + ISNULL(p4, 0) + ISNULL(p5, 0) ELSE 0 END)) AS rd(Calc)
 GROUP BY Region, DATEPART(YEAR, i.d1);
-1
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'
);
WITH RawData
     AS (SELECT i.region,
                i.d1,
                DATEPART(year, d1) AS YearNumber,
                ISNULL(p1, 0) + ISNULL(p2, 0) + ISNULL(p3, 0) + ISNULL(p4, 0) + ISNULL(p5, 0) AS Calc,
                necombs
         FROM @info i)
     SELECT rd.region,
            rd.YearNumber,
            SUM(rd.Calc) / ISNULL(SUM(ISNULL(necombs, 0)), 0) Answer
     FROM RawData rd
     WHERE YearNumber = '2015'
     GROUP BY Region,
              YearNumber

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.