SQL query help - SQL Server Forums

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

SQL query help

Author

Topic

Dan1978
Starting Member

5 Posts

Posted - 2010-10-19 : 06:28:08

I have set up the below query to try and replicate the figures ate the bottom of the mail, but when I try to perform the % calculation (highlighted in bold) i get the follwing error message 'Divide by zero error encountered'. Does anyone know how I can overcome this error? Help would be greatly appreciated.

SELECT Users.FirstName ,
Users.Surname ,
SUM(CAST([3Directorder].[Insurance?] AS INT)) as InsuranceOfferedCount,
SUM(CAST([3directorder].InsuranceSent AS INT)) as InsuranceSoldCount,
SUM(CAST([3Directorder].[Insurance?] AS INT)) / SUM(CAST([3directorder].InsuranceSent AS INT)) *100 as Percentage
FROM Users
INNER
JOIN [3directorder]
ON [3directorder].AgentID = Users.UserID
where [3directorder].StartDate between '2010-10-04 00:00:01.000' and '2010-10-09 00:00:00.000'
group by Users.FirstName, Users.Surname

(These are the figures I am trying to create)

FirstName Surname InsuranceOfferedCount InsuranceSoldCount Percentage
Gareth Blackwell 10 5 50
Nathan Blake 5 1 20
Kirby Boswell 11 4 36
Dale Bowen 6 4 67
Janine Brown 12 8 67
Natalie Bungay 12 8 67
Adam Cameron 7 2 29
Louis Cann 12 9 75
Edward Carter 2 0 0
Nathan Challoner 10 7 70
Joe Coffey 2 1 50
Kayley Coleman 0 0 0

maevr
Posting Yak Master

169 Posts

Posted - 2010-10-19 : 06:47:31

Please post table structure and sample data!

Are you sure the InsuranceSent does not values that in not compatible with divide?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 12:48:38

make it like

ISNULL(SUM(CAST([3Directorder].[Insurance?] AS INT)) / NULLIF(SUM(CAST([3directorder].InsuranceSent AS INT)),0) *100,0) as Percentage

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources