T-SQL IF statement embedded in a sum() function

Question

I'm attempting to convert a MySQL query to a T-SQL query and the IF statement that's enclosed within a SUM statement is tripping me up. Any suggestions?

SELECT
    CMTS_RQ.[Dated],
    CMTS_RQ.CMTS_Name,
    Count(CMTS_RQ.CMTS_Name) AS emat_count,
    Sum(if(CMTS_RQ.US_Pwr>=37 and CMTS_RQ.US_Pwr<=49)) AS us_pwr_good
FROM
    CMTS_RQ
GROUP BY
    CMTS_RQ.CMTS_Name,
    CMTS_RQ.[Dated]

But I get an error:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.

marc_s · Accepted Answer · 2013-05-13 18:48:34Z

21

T-SQL doesn't have a "inline" IF statement - use a CASE instead:

SELECT
    CMTS_RQ.[Dated],
    CMTS_RQ.CMTS_Name,
    Count(CMTS_RQ.CMTS_Name) AS emat_count,
    Sum(CASE 
           WHEN CMTS_RQ.US_Pwr >=37 AND CMTS_RQ.US_Pwr <= 49 
             THEN 1
             ELSE 0 
        END) AS us_pwr_good
FROM
    CMTS_RQ
GROUP BY
    CMTS_RQ.CMTS_Name,
    CMTS_RQ.[Dated]

So if the value of CMTS_RQ.US_Pwr is >= 37 AND <= 49 then add 1 to the SUM - otherwise 0. Does that give you what you're looking for?

In SQL Server 2012 and newer, you can use the new IIF function:

SUM(IIF(CMTS_RQ.US_Pwr >= 37 AND CMTS_RQ.US_Pwr <= 49, 1, 0)) AS us_pwr_good

edited May 13, 2013 at 18:48

answered May 13, 2013 at 18:43

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user937036 Over a year ago

Thanks for the insight, that points me in the right direction.

Collectives™ on Stack Overflow

T-SQL IF statement embedded in a sum() function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related