?
Solved

SQL Syntax IIF in select to prevent Divide by Zero

Posted on 2011-03-24
5
Medium Priority
?
616 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Gray5452
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 35209612
case when sum(calls)>0 then (SUM(cost)/SUM(calls)),0) else 0 end as 'CPC'
0
 
LVL 9

Accepted Solution

by:
Roman Gherman earned 1000 total points
ID: 35209733
case when sum(calls)>0
THEN (SUM(cost)/SUM(calls))
ELSE 0
END AS CPC
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 35209841
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
 
LVL 41

Expert Comment

by:Sharath
ID: 35210594
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
 

Author Closing Comment

by:Gray5452
ID: 35234709
both worked perfectly,,I'd be lost without this site..

Thanks Again..
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question