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

sql-server-2008-r2

I am trying to run this syntax, but I get an error of

Msg 4145, Level 15, State 1, Line 9
An expression of non-boolean type specified in a context where a condition is expected, near 'SUM'.

And this is my syntax, just trying to conditionally execute a SUM() based off the criteria.

SELECT
    CASE WHEN
         SUM(case when raaa.EN = 'Yes' AND raaa.HR >= raaa.WT then raaa.WT end)
         SUM(case when raaa.EN = 'Yes' AND raaa.HR <= raaa.WT then raaa.HR end)
         SUM(case when raaa.EN = 'No' AND raaa.HR <= raaa.WT then raaa.HR end)
         SUM(case when raaa.EN = 'No' AND raaa.HR >= raaa.WT then raaa.WT end)
    END AS HR
FROM
        ranaboveaboardabout raaa;

EDIT
I know that this will work, just can't seem to get syntax correct for the above

Select  
CASE WHEN 
        SUM(raaa.field1) / SUM(CASE WHEN raaa.field2 < raaa.field1 THEN raaa.field2 ELSE raaa.field1 END) >= 16.23 THEN 20
        ELSE SUM(raaa.field1) / SUM(CASE WHEN raaa.field2 < raaa.field1 THEN raaa.field2 ELSE raaa.field1 END)
END AS nestedSUM
FROM
    ranaboveaboardabout raaa;

Best Answer

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
Related Question