Math in select statement - 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 2008 Forums

Transact-SQL (2008)

Math in select statement

Author

Topic

Really Obfuscated
Starting Member

6 Posts

Posted - 2015-03-17 : 07:33:00

I have the following:

SELECT isnull (Sum((Convert(numeric(8,2), (S.FinishLength/12) * T.FootWeight) / 2000)),0) AS Tons,
isnull(Sum(case when S.RepairCode like '%A%' then(Convert(numeric(8,2), (S.FinishLength/12) * T.FootWeight) / 2000)end),0) AS A_Tons,

-- Here is where I need (A_Tons / Tons) as A_Percent

FROM NYS2Reheat R INNER JOIN NYS2SawPieces S ON R.RecordID = S.RollRecID INNER JOIN TensileProducts T ON R.FinalProd = T.SQLProduct
Where S.ShiftIdent like '" & day1 & "254B' and S.Scrapped = 'N'

I need to divide A_Tons by Tons to get a percentage, but when Tons = 0 then I get a division by zero error.
How can I "work-around" that?

Obfuscated beyond repair.

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-03-17 : 08:08:20

Take a look at NULLIF

example:

SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio
FROM school_clubs;

Any number divided by NULL gives NULL, and no error is generated.

We are the creators of our own reality!

Really Obfuscated
Starting Member

6 Posts

Posted - 2015-03-17 : 09:14:13

Won't work, I'm sending data to DBGrid controlled by an Adodc. The grid won't accept null values. (I may be trying something that needs a new control.) I'm a fairly new SQL user and don't understand, yet, some of the environments.
I get the error "Object doesn't support this property or method".

Obfuscated beyond repair.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-17 : 09:59:58

then wrap males/NULLIF(females, 0) in isnull like this:

ISNULL(males/NULLIF(females, 0), 'default value in case females = 0')

Really Obfuscated
Starting Member

6 Posts

Posted - 2015-03-25 : 07:25:20

Got it to work. Thanks for the help.
Trying to find where to mark this topic as

"Solved"

Obfuscated beyond repair.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources