CODEWITHSUNDEEP
sqlsql-server-2008ms-accesscase
Welldonebacon
Welldonebacon

Reputation: 83

MS Access SUMIIF to SQL CASE statement

I'm trying to convert a MS Access query to SQL, I'm struggling to find the correct way of doing this.

The MS Access line is: Records Loaded: Sum(IIf([cmp_CampaignTable].>[ContactID]>0,1,0))

The SQL code I have so far is:

SELECT        Project.ProjectName, 
                cmp_UKWarranty.ProjectID, 
                SUM(CONVERT(numeric,(CASE WHEN (cmp_UKWarranty.ContactID) >0,1,0) AS RecordsLoaded
FROM            cmp_UKWarranty INNER JOIN
                         Project ON cmp_UKWarranty.ProjectID = Project.ProjectID INNER JOIN
                         Dial ON cmp_UKWarranty.DialID = Dial.DialID CROSS JOIN
                         CRC

I have no one else to ask, is anyone able to point me in the right direction here?

Adam

Upvotes: 1

Views: 447

Answers (2)

Deep
Deep

Reputation: 3202

Your case when syntax is wrong :

SELECT Project.ProjectName,
       cmp_UKWarranty.ProjectID,
       Sum(CASE
             WHEN ( cmp_UKWarranty.ContactID > 0 ) THEN 1
             ELSE 0
           END)
FROM   cmp_UKWarranty
       INNER JOIN Project
               ON cmp_UKWarranty.ProjectID = Project.ProjectID
       INNER JOIN Dial
               ON cmp_UKWarranty.DialID = Dial.DialID
       CROSS JOIN CRC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270483

You are almost there. You need the then and else. Also, the conversion to "numeric" is unnecessary:

SELECT p.ProjectName, w.ProjectID,
       SUM(CASE WHEN w.ContactID > 0 THEN 1 ELSE 0) AS RecordsLoaded
FROM cmp_UKWarranty w INNER JOIN
     Project p
     ON w.ProjectID = p.ProjectID INNER JOIN
     Dial d
     ON w.DialID = Dial.d CROSS JOIN
     CRC

Upvotes: 1

Related Questions