divide by zero error - help please!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

divide by zero error - help please!

Author

Topic

squarefish
Starting Member

28 Posts

Posted - 2009-03-13 : 06:52:39

Hi there I have a sql statement that I am calculating percentages with. The reason I am using sql to calculate the percentages is so I can sort it very easily into highest pecent first etc.

The problem is that sometimes kccprofiles is a 0 so I get a divide by zero error.

Is there any way of doing something like
if kccprofiles = 0 then write 0 else write kccprofiles /(salesNewEnquiriesWalkin + salesUsedEnquiriesWalkin)

Below is the statement

SELECT dbo.docBranchData.branchName, dbo.docSales.salesNewEnquiriesWalkin + dbo.docSales.salesUsedEnquiriesWalkin AS phoenixenquiries,
dbo.kccprofiles.kccprofiles, (dbo.kccprofiles.kccprofiles * 100.0) / (dbo.docSales.salesNewEnquiriesWalkin + dbo.docSales.salesUsedEnquiriesWalkin)
AS percentloaded, dbo.kccprofiles.kccemails, dbo.kccprofiles.kccemails * 100 / dbo.kccprofiles.kccprofiles AS percentemails,
dbo.docSales.salesTimestamp
FROM dbo.docBranchData LEFT OUTER JOIN
dbo.kccprofiles ON dbo.docBranchData.branchID = dbo.kccprofiles.dealercode LEFT OUTER JOIN
dbo.docSales ON dbo.docBranchData.branchID = dbo.docSales.salesBranchID

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-13 : 07:00:08

[code]SELECT
dbo.docBranchData.branchName,
dbo.docSales.salesNewEnquiriesWalkin + dbo.docSales.salesUsedEnquiriesWalkin AS phoenixenquiries,
dbo.kccprofiles.kccprofiles,
case when (dbo.docSales.salesNewEnquiriesWalkin + dbo.docSales.salesUsedEnquiriesWalkin) =0 then 0 else (dbo.kccprofiles.kccprofiles * 100.0) / (dbo.docSales.salesNewEnquiriesWalkin + dbo.docSales.salesUsedEnquiriesWalkin) end AS percentloaded,
dbo.kccprofiles.kccemails,
case when dbo.kccprofiles.kccprofiles =0 then 0 else dbo.kccprofiles.kccemails * 100 / dbo.kccprofiles.kccprofiles end AS percentemails,
dbo.docSales.salesTimestamp
FROM dbo.docBranchData LEFT OUTER JOIN
dbo.kccprofiles ON dbo.docBranchData.branchID = dbo.kccprofiles.dealercode LEFT OUTER JOIN
dbo.docSales ON dbo.docBranchData.branchID = dbo.docSales.salesBranchID[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 07:00:17

[code]SELECT dbd.branchName,
ds.salesNewEnquiriesWalkin + ds.salesUsedEnquiriesWalkin AS phoenixenquiries,
kp.kccprofiles,
case ds.salesNewEnquiriesWalkin + ds.salesUsedEnquiriesWalkin
when 0 then 0
else kp.kccprofiles * 100.0E / (ds.salesNewEnquiriesWalkin + ds.salesUsedEnquiriesWalkin)
end AS percentloaded,
kp.kccemails,
kp.kccemails * 100.0E / kp.kccprofiles AS percentemails,
ds.salesTimestamp
FROM dbo.docBranchData AS dbd
LEFT JOIN dbo.kccprofiles AS kp ON kp.dealercode = dbd.branchID
LEFT JOIN dbo.docSales AS ds ON ds.salesBranchID = dbd.branchID[/code]

E 12°55'05.63"
N 56°04'39.26"

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-03-13 : 09:05:10

its the denominator thats causing this issue and not the numerator, and here's is a third variation to avoid this


SELECT dbo.docbranchdata.branchname, 
       dbo.docsales.salesnewenquirieswalkin + dbo.docsales.salesusedenquirieswalkin AS phoenixenquiries, 
       dbo.kccprofiles.kccprofiles, 
       Isnull((dbo.kccprofiles.kccprofiles * 100.0) / Nullif((dbo.docsales.salesnewenquirieswalkin + dbo.docsales.salesusedenquirieswalkin), 
                                                             0), 
              0) AS percentloaded, 
       dbo.kccprofiles.kccemails, 
       dbo.kccprofiles.kccemails * 100 / dbo.kccprofiles.kccprofiles                AS percentemails, 
       dbo.docsales.salestimestamp 
FROM   dbo.docbranchdata 
       LEFT OUTER JOIN dbo.kccprofiles 
         ON dbo.docbranchdata.branchid = dbo.kccprofiles.dealercode 
       LEFT OUTER JOIN dbo.docsales 
         ON dbo.docbranchdata.branchid = dbo.docsales.salesbranchid

squarefish
Starting Member

28 Posts

Posted - 2009-03-13 : 09:41:44

wow thanks guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources