Solved

Avoiding SQL divide by zero exception

Posted on 2007-03-26
7
296 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 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

16 Experts available now in Live!

Get 1:1 Help Now