T-SQL IF statement built into sum () function

Question

I am trying to convert a MySQL query into a T-SQL query, and the IF statement enclosed in the SUM statement disables me. 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 message:

Msg 156, Level 15, State 1, Line 5
Invalid syntax next to "if" keyword.
Msg 102, Level 15, State 1, Line 5
Invalid syntax next to ')'.

Answer 1

T-SQL does not have an inline IF - use 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 SUM - otherwise 0. Does this give you what you are looking for?

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

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