Avoiding SQL divide by zero exception

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!
sundrejserAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
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
Anthony PerkinsCommented:
P.S.

This:
from A

Should read:
from
(
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sundrejserAuthor Commented:
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
Anthony PerkinsCommented:
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
sundrejserAuthor Commented:
But all cpa values are returned as 0
0
Anthony PerkinsCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.