Math functions - 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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Math functions

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-10-30 : 10:58:53

Hi,

Im trying to perform afew math functions, but because there afew 0 values I get error for cannot parse or just get 0 for all values. Ive used the checknull function but still does not make difference. Any tips??

Example below:

Select SUM(dbo.AttStudReg.Actual) AS Actual, SUM(dbo.AttStudReg.Meets) AS Meets, ROUND((dbo.AttStudReg.Actual/dbo.AttStudReg.Meets)*100,2) AS Percentage.
FROM AttStudReg

Thanks
Cipriani

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-30 : 12:11:51

Use CASE statement - I've put else '0' to show instead of divide by zero error - but you can put something else here to show error caught eg '-999'

Select SUM(dbo.AttStudReg.Actual) AS Actual, SUM(dbo.AttStudReg.Meets) AS Meets, case when dbo.AttStudReg.Meets > 0 then ROUND((dbo.AttStudReg.Actual/dbo.AttStudReg.Meets)*100,2) else '0' end AS Percentage
FROM dbo.A
group by ADID,M

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-10-30 : 13:04:47

Still didnt work :( still only divides the ones that match like 6 and 6 but any that are like 1/6, 4/6 gives 0 because of the 0's in there.

quote:
Originally posted by darkdusky

Use CASE statement - I've put else '0' to show instead of divide by zero error - but you can put something else here to show error caught eg '-999'

Select SUM(dbo.AttStudReg.Actual) AS Actual, SUM(dbo.AttStudReg.Meets) AS Meets, case when dbo.AttStudReg.Meets > 0 then ROUND((dbo.AttStudReg.Actual/dbo.AttStudReg.Meets)*100,2) else '0' end AS Percentage
FROM dbo.A
group by ADID,M

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 02:37:15

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-10-31 : 08:07:19

thanks for that but I still get error for divide by zero encountered cos if there is a 0 value 0 x 1.0 is 0 still.

quote:
Originally posted by madhivanan

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-10-31 : 08:15:36

fixed it, fixed it

quote:
Originally posted by cipriani1984

thanks for that but I still get error for divide by zero encountered cos if there is a 0 value 0 x 1.0 is 0 still.

quote:
Originally posted by madhivanan

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 11:29:52

a common way to avoid divide by zero error is to use NULLIF in denominator to convert 0 values to NULL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources