Solved

Avoiding SQL divide by zero exception

Posted on 2007-03-26
7
298 Views
Last Modified: 2012-01-05
I have this stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ea_getTop5CPC]
AS
select TOP 5 programId, sum(cpa) as cpa, (convert(float,sum(cpa)) / convert(float,sum(clicks))) as cpc
from
(
 select programId, sum(cpa) as cpa, 0 as clicks
 from ea_Impressions(nolock)
 where impressionDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, count(*) as clicks
 from ea_Clicks (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, 0 as clicks
 from ea_Leads (nolock)
 where leadDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, 0 as clicks
 from ea_Sales (nolock)
 where saleDateTime >= dateadd(day,-30,getdate())
 group by programId


) tmp group by programId ORDER BY cpa DESC;

In some cases "convert(float,sum(clicks))) " = 0 and a "Divide by zero" error is thrown... How can I change the procedure so cpc is set to 0 when "convert(float,sum(clicks))) " = 0?

Hope someone can help me... Please!
0
Comment
Question by:sundrejser
  • 5
  • 2
7 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 50 total points
ID: 18794640
select      TOP 5 programId,
      sum(cpa) as cpa,
      CASE
            WHEN sum(clicks) > 0 THEN convert(float,sum(cpa)) / convert(float,sum(clicks))
            ELSE 0
      END as cpc
from A
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18794651
Incidentally, if you don't want to see and approximation side-effects, replace float with a fixed data type such as numeric (decimal).

Also, look into the differences between UNION and UNION ALL.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18794669
P.S.

This:
from A

Should read:
from
(
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:sundrejser
ID: 18795268
I've changed the procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ea_getTop5CPC]
AS
select TOP 5 programId,
      sum(cpa) as cpa,
      CASE
            WHEN sum(clicks) > 0 THEN convert(float,sum(cpa)) / convert(float,sum(clicks))
            ELSE 0
      END as cpc
from
(
 select programId, sum(cpa) as cpa, 0 as clicks
 from ea_Impressions(nolock)
 where impressionDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, count(*) as clicks
 from ea_Clicks (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, 0 as clicks
 from ea_Leads (nolock)
 where leadDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, 0 as clicks
 from ea_Sales (nolock)
 where saleDateTime >= dateadd(day,-30,getdate())
 group by programId


) tmp group by programId ORDER BY cpa DESC;



Now it returns a CPC of 0, even if there's rows in the ea_clicks table
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18796242
Than either there are no rows in the ea_clicks table in the last 30 days and/or they are not in the TOP 4 SUM(cpa)
0
 

Author Comment

by:sundrejser
ID: 18798003
But all cpa values are returned as 0
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18800283
I suggest you post the results of the following query:

select TOP 20 programId,
      sum(cpa) as cpa,
      sum(clicks)
from
(
 select programId, sum(cpa) as cpa, 0 as clicks
 from ea_Impressions(nolock)
 where impressionDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, count(*) as clicks
 from ea_Clicks (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, 0 as clicks
 from ea_Leads (nolock)
 where leadDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, 0 as clicks
 from ea_Sales (nolock)
 where saleDateTime >= dateadd(day,-30,getdate())
 group by programId

) tmp
group by programId
ORDER BY cpa DESC
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

920 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

18 Experts available now in Live!

Get 1:1 Help Now