Divide by zero error encountered.

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

General SQL Server Forums

New to SQL Server Programming

Divide by zero error encountered.

Author

Topic

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-10-31 : 16:21:34

Hi,

I have one table with columns patientName , Nurse,ArrivalDate, DepartDate . It has all the patiet information for all the Nurses.

I am calculating Average Number of patients per day per nurse by using below query

SELECT Nurse,
COUNT(DISTINCT patientName) AS NoOfPatients,
COUNT(DISTINCT patientName) * 1.0/COUNT(DISTINCT ArrivalDate) AS [AvgNo.ofpatientsPerDay]
FROM Table t
GROUP BY NURSE

but if ArrivalDate is Null I am getting below error saying that

Divide by zero error encountered.

How can i over come this problem?

Can any one please help me on this?

Thanks

Archana

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-31 : 16:23:37

one way - if you prefer to get a NULL when arrivalDate is null is:

replace: COUNT(DISTINCT ArrivalDate)
with: NullIf(COUNT(DISTINCT ArrivalDate),0)

Be One with the Optimizer
TG

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-10-31 : 16:27:24

I have one more field in the table called DepartDate .

If ArrivalDate is Null i need to check for DepartDate to calculate the AvgNo.ofpatientsPerDay.

Thanks

Archana

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-31 : 16:32:30

use COALESE function

Be One with the Optimizer
TG

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-31 : 16:33:49

Change TG's code to this:

NullIf(COUNT(DISTINCT COALESCE(ArrivalDate,DepartDate)),0)

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-10-31 : 16:38:26

Thank you James :-)

It worked.

Archana

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources