Solved

Avoiding SQL divide by zero exception

Posted on 2007-03-26
7
299 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Simple SQL query from two tables 13 54
Why Delete * statement wont work with sql server ? 6 42
Query Help - MSSQL - Averages 5 27
Rename a column in the output 3 14
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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

809 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