SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Error: Divide by zero error encountered.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

simflex
Constraint Violating Yak Guru

319 Posts

Posted - 05/15/2014 :  10:54:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1472 Posts

Posted - 05/15/2014 :  11:28:19  Show Profile  Reply with Quote
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
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1770 Posts

Posted - 05/15/2014 :  11:29:34  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/15/2014 :  11:37:08  Show Profile  Reply with Quote
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
Go to Top of Page

simflex
Constraint Violating Yak Guru

319 Posts

Posted - 05/15/2014 :  11:54:24  Show Profile  Reply with Quote
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'.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1472 Posts

Posted - 05/15/2014 :  11:56:52  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/15/2014 :  12:11:48  Show Profile  Reply with Quote
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?
Go to Top of Page

simflex
Constraint Violating Yak Guru

319 Posts

Posted - 05/15/2014 :  12:22:45  Show Profile  Reply with Quote
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


Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1472 Posts

Posted - 05/15/2014 :  12:36:39  Show Profile  Reply with Quote
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.
Go to Top of Page

simflex
Constraint Violating Yak Guru

319 Posts

Posted - 05/15/2014 :  12:44:48  Show Profile  Reply with Quote
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.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1472 Posts

Posted - 05/15/2014 :  12:59:37  Show Profile  Reply with Quote
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?
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1472 Posts

Posted - 05/15/2014 :  13:12:15  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/15/2014 :  13:41:02  Show Profile  Reply with Quote
You need a group by on the Name.

Go to Top of Page

simflex
Constraint Violating Yak Guru

319 Posts

Posted - 05/15/2014 :  14:08:20  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/15/2014 :  14:13:25  Show Profile  Reply with Quote
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
Go to Top of Page

simflex
Constraint Violating Yak Guru

319 Posts

Posted - 05/15/2014 :  15:43:16  Show Profile  Reply with Quote
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.
Go to Top of Page

simflex
Constraint Violating Yak Guru

319 Posts

Posted - 05/15/2014 :  19:59:07  Show Profile  Reply with Quote
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
Go to Top of Page

simflex
Constraint Violating Yak Guru

319 Posts

Posted - 05/15/2014 :  21:36:54  Show Profile  Reply with Quote
My bad, sorry. Just one correction.

My table does not have a Votes fieldname.

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

sorry
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/16/2014 :  11:10:14  Show Profile  Reply with Quote
So what should the output be? 2 votes and 50% for both?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/16/2014 :  11:13:57  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000