Solved

Avoiding SQL divide by zero exception

Posted on 2007-03-26
7
300 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

828 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