SQL Syntax IIF in select to prevent Divide by Zero

getting this error

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '>'.

when using this syntax


      SELECT sitename,
               campaign,
               SUM(cost)as sitecost,
               SUM(calls)as sitecalls,
               
               IIF(sum(calls)>0,(SUM(cost)/SUM(calls)),0) as 'CPC'
               
      FROM [KoolSmiles].[dbo].[v_DigitalMedia]
      where (REportDate between '3/14/11' and '3/20/11')and sitename = 'Google'--where (REportDate between @start and @end)and sitename = 'Google'
      group by sitename,campaign
Gray5452Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Roman GhermanConnect With a Mentor Senior Software EngineerCommented:
case when sum(calls)>0
THEN (SUM(cost)/SUM(calls))
ELSE 0
END AS CPC
0
 
chapmandewCommented:
case when sum(calls)>0 then (SUM(cost)/SUM(calls)),0) else 0 end as 'CPC'
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should do:
SELECT sitename,
               campaign,
               SUM(cost)as sitecost,
               SUM(calls)as sitecalls,
               
               (SUM(cost)/NULLIF(SUM(calls),0) as 'CPC'
               
      FROM [KoolSmiles].[dbo].[v_DigitalMedia]
      where (REportDate between '3/14/11' and '3/20/11')
        and sitename = 'Google'
      group by sitename,campaign

Open in new window

0
 
SharathData EngineerCommented:
if you don't want NULL as cpc, you can try ISNULL.
SELECT sitename,
               campaign,
               SUM(cost)as sitecost,
               SUM(calls)as sitecalls,
               
               isnull(SUM(cost)/NULLIF(SUM(calls),0),0) as 'CPC'
               
      FROM [KoolSmiles].[dbo].[v_DigitalMedia]
      where (REportDate between '3/14/11' and '3/20/11')
        and sitename = 'Google'
      group by sitename,campaign

Open in new window

0
 
Gray5452Author Commented:
both worked perfectly,,I'd be lost without this site..

Thanks Again..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.