asked on

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

chapmandew

case when sum(calls)>0 then (SUM(cost)/SUM(calls)),0) else 0 end as 'CPC'

ASKER CERTIFIED SOLUTION

Roman Gherman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharath S

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

Gray5452

ASKER

both worked perfectly,,I'd be lost without this site..

Thanks Again..