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

sql servertsql

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 ')'.

Best Answer

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

Related Solutions

Sql-server – LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

As per the documentation: FROM (Transact-SQL):

<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    JOIN

The keyword OUTER is marked as optional (enclosed in square brackets). In this specific case, whether you specify OUTER or not makes no difference. Note that while the other elements of the join clause is also marked as optional, leaving them out will make a difference.

For instance, the entire type-part of the JOIN clause is optional, in which case the default is INNER if you just specify JOIN. In other words, this is legal:

SELECT *
FROM A JOIN B ON A.X = B.Y

Here's a list of equivalent syntaxes:

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

Also take a look at the answer I left on this other SO question: SQL left join vs multiple tables on FROM line?.

enter image description here

Sql – Insert results of a stored procedure into a temporary table

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

Best Answer

Related Solutions

Sql-server – LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

Sql – Insert results of a stored procedure into a temporary table

Related Topic