Solved

SQL Syntax IIF in select to prevent Divide by Zero

Posted on 2011-03-24
5
606 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 143

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 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 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