Solved

SQL Syntax IIF in select to prevent Divide by Zero

Posted on 2011-03-24
5
572 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
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 250 total points
ID: 35209733
case when sum(calls)>0
THEN (SUM(cost)/SUM(calls))
ELSE 0
END AS CPC
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 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 40

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now