Sql-server – SUM Mixed With Case In Select Statement

sql serversql-server-2008-r2t-sql

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

Best Answer

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);

Related Solutions

Sql-server – Nest SUM() Function Inside Case Statement

You need to remove the outer case statement. It should be:

Select sum( case when ..... then .... end) from foo

You have

Select case when sum(case when.... then .... end) end from foo

The when clause expects something that will resolve to either true or false. That's what your error message is trying to tell you.

I'm still not 100% sure what your end goal is but you either need to remove the wrapping case statement like I said above or pull the case condition out of the sum.

Select
   Case 
       When <condition > then sum(<column>)
        When <condition > then sum(<column >)
         Else sum(<column >)
         End
From foo

Sql-server – Case Statement With Group By

Untested, but I believe your query should look like:

SELECT salesman
      ,sum(case when DATEPART(Year, warrantypurchasedate) = '2015'
                 and DATEPART(Quarter, warrantypurchasedate) = '1'
                then itemprice 
           end) As [Q1 2015]
      ,sum(case when DATEPART(Year, warrantypurchasedate) = '2016'
                 and DATEPART(Quarter, warrantypurchasedate) = '1'
                then itemprice 
           end) As [Q1 2016]
FROM Barnacles
WHERE DATEPART(Year, warrantypurchasedate) in ('2015', '2016')
  AND DATEPART(Quarter, warrantypurchasedate) = '1' 
GROUP BY salesman

I moved sum outside of case, and added a WHERE clause to filter out relevent rows. I also removed date from the GROUP BY clause, otherwise you will get 1 row per date.

Best Answer

Related Solutions

Sql-server – Nest SUM() Function Inside Case Statement

Sql-server – Case Statement With Group By

Related Question