Error: 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

SQL Server 2008 Forums

Transact-SQL (2008)

Error: Divide by zero error encountered.

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-05-15 : 10:54:29

Hi Experts,

When I run the code below:


"select count(*),(100.0 * (cast(count(*) as float) / SUM(count(*)) over ())) from Results where resultsid=@cid

we are getting Divide by zero error encountered error message.

Does anyone know how to fix this?

We are basically trying to get the totalcount for each user and percentage of that count.

For instance,

Total Count %Total
User1 40 40%
User2 30 30%
User3 30 30%

Thanks alot in advance

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-15 : 11:28:19

1. decide what you want to return if "SUM(count(*)) over ()" returns 0.
2. use a CASE statement for the second column returned. e.g.


select count(*)
     , case when  0 <> (select count(*) from Results where resultsid=@cid)
       then (100.0 * (cast(count(*) as float) / SUM(count(*)) over ()))
       else 42--whatever you want when the count is 0
       end
from Results where resultsid=@cid

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-05-15 : 11:29:34

Short answer: Stop dividing by zero.
Long answer: What value are you getting for "SUM(count(*)) over ()"? You could use CASE to test for zero and substitute a non-zero value.

===============================================================================
“Opportunity is missed by most people because it is dressed in overalls and looks like work.” - T.A.Edison

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-15 : 11:37:08

You can use NULLIF to prevent the device by zero. If you want to prevent a null value, then you can also wrap that in a COALESCE:

select 
	count(*),
	(100.0 * (cast(count(*) as float) / NULLIF(SUM(count(*)) over (), 0))) 
from 
	Results 
where 
	resultsid=@cid


select 
	count(*),
	COALESCE((100.0 * (cast(count(*) as float) / NULLIF(SUM(count(*)) over (), 0))), 0)
from 
	Results 
where 
	resultsid=@cid

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-05-15 : 11:54:24

Thank you good people for your kindness.

Lamprey, I tried your code and got the following error:

Conversion from type 'DBNull' to type 'String' is not valid

I actually tried nullIf before, mine didn't work. I think I was getting similar errors.

gbritton, yours gave me an error too:

Incorrect syntax near the keyword 'when'.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-15 : 11:56:52

If my solution gives an error, then you may have mis-copied it. I just ran it again (copied and pasted from my post above) and it runs fine.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-15 : 12:11:48

quote:
Originally posted by simflex

Thank you good people for your kindness.

Lamprey, I tried your code and got the following error:

Conversion from type 'DBNull' to type 'String' is not valid

I actually tried nullIf before, mine didn't work. I think I was getting similar errors.

What generated that error? Are you calling a sproc from another environment or are you not using SQL Server?

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-05-15 : 12:22:45

Yes, you are right - I did mis-copy it.

I sincerely apologize for that.

The issue though is that it is not producing the correct result.

Firstly, it is now asigning each user a total of 1 count.

Example, the names below have these actual values currently when you just run select count(*) from results where resultsId=@cid

¦Mar Brown - 19
¦Mary Hart- 11
¦Ann Jones - 0

It is when we try to get percentage for each total user count that everything gets messed up

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-15 : 12:36:39

Well, in your example data, each user does indeed appear once, so count(*) =1 for each cid. Perhaps you should show what results you expect from your query.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-05-15 : 12:44:48

Below is the sample result I expect:

¦Mark Fowler - 31(100%)
¦Alicia Keys - 7(100%)
¦Mark Jackson - 19(100%)
¦Mindy Gains - 11(100%)

The above example is actually the result from Lamprey's code.

It is getting the correct count for each user but the percentage is 100% across the board. From the example above, Mark Fowler should get higher percentage, followed by Mark Jackson, followed by Mindy Gains and Alicia Keys brings up the rear with total percentage equalling 100%.

Right now everyone gets 100% which is wrong.

Lamprey, your code was not working before because one user has null values. I removed that user and hence it works.

If you guys can help with the percentage issue, I would really appreciate it.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-15 : 12:59:37

Can you post your sample data as an INSERT INTO statement? That would be easier to work with. Also, what to you set @cid to when you run the query?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-15 : 13:12:15

quote:
Originally posted by gbritton

Can you post your sample data as an INSERT INTO statement? That would be easier to work with. Also, what to you set @cid to when you run the query?

Here's a variation that may do what you want:


select count(*)
     , case when  0 <> (select count(*) from Results)
       then (100.0 * (cast(count(*) as float) / (select count(*) from Results))
)      else 42--whatever you want when the count is 0
       end
from Results where resultsid=@cid

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-15 : 13:41:02

You need a group by on the Name.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-05-15 : 14:08:20

Thank you experts for all your help.

gbritton, your solution is almost there but still not right.

Here is sample of what it looks like:

¦Mark Fowler - 31(1.27572016460905%)
¦Alicia Keys - 7(0.288065843621399%)
¦Mark Jackson - 19(0.781893004115226%)
¦Mindy Gains - 11(0.452674897119342%)

This is actual results we are getting.

Lampley, I can't group by name because name is on a different table.

I did try to group by id though since each ID is associate with each name and the IDs are in results table.

No difference.

Thanks guys for your patience.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-15 : 14:13:25

Can you post sample data and expected output so we can run queries against it? This is far too much back and forth for something seemingly so simple. Here is a sample:

DECLARE @Foo TABLE (UserID INT, Val INT)

INSERT @Foo
VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(2, 5),
(2, 6),
(3, 7),
(3, 8),
(3, 9),
(3, 10),
(3, 11),
(3, 12),
(4, 13),
(5, 14)

-- Works
SELECT 
	COUNT(*),(100.0 * (cast(count(*) as float) / SUM(count(*)) over ())) 
FROM @Foo
GROUP BY UserID

-- Doesn't work
SELECT 
	COUNT(*),(100.0 * (cast(count(*) as float) / SUM(count(*)) over ())) 
FROM @Foo

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-05-15 : 15:43:16

Thank you guys so much for your help.

I will try and put together dummy data that is a replica of our actual data. Current database, as is, is confidential.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-05-15 : 19:59:07

Guys, sorry for the delay.

Please use this as sample data:


create table	Candidates	
( candidateid int
, CandidateName varchar(10)
, CurrentOfficeHolder varchar(10)
, PositionId int
);
create table	ElectionResults	
( id int
, candidateid int
, votes int
);

insert Candidates select 1,'Joe','Incumbent',1
insert Candidates select 2,'Bud','Incumbent',1

insert ElectionResults select 1,1,23;
insert ElectionResults select 2,1,56;
insert ElectionResults select 3,2,99;
insert ElectionResults select 4,2,100;

sample output:


CANDIDATEID 	CANDIDATENAME 	VOTES 	PERCENTVOTES
1 		Joe (Incumbent)	79 	28.41726618705
2 		Bud (Incumbent)	199 	71.58273381295

Only difference is that we would like the code to only show Votes and percentageVotes

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-05-15 : 21:36:54

My bad, sorry. Just one correction.

My table does not have a Votes fieldname.

so sample data should be count(*) instead of votes.

sorry

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-16 : 11:10:14

So what should the output be? 2 votes and 50% for both?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-16 : 11:13:57

[code]SELECT
C.candidateid
,C.CandidateName
,COUNT(*) AS Votes
,100.0 * (cast(count(*) as float) / NULLIF(SUM(count(*)) over (), 0)) AS PercentVotes

FROM
Candidates AS C
INNER JOIN
ElectionResults AS E
ON C.candidateid = E.candidateid
GROUP BY
C.candidateid
,C.CandidateName[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources