CODEWITHSUNDEEP
sql-server-2008
user3009565
user3009565

Reputation: 11

SQL Server Zero Values

I am pretty green at this stuff and have a problem with what I thought was a simple select and calculate SQL Formula.

The issue being is that in my table I have a lot of zero values, sound's wierd I know but this is how it is.

The formula is to calculate a percentage marin based on two columns these being Total and Costs, whilst my simple formula works if there's a value, when theres a zero value absolutely nothing works.

The formula I am using is this ((Total-Costs)/Total)*100

Can anyone advise this greenhorn how to overcome the zero value issue?

Upvotes: 1

Views: 48

Answers (2)

M.Ali
M.Ali

Reputation: 69524

You could do something like this 

SELECT COALESCE((Total- Costs / NULLIF(Total,0))*100, 0)

Upvotes: 1

Kyle Hale
Kyle Hale

Reputation: 8120

If Total has a 0 in it, you'll get a divide by zero error. You must catch these using a CASE statement, like so

SELECT
CASE WHEN Total = 0 then 0 else ((Total-Costs)/Total)*100 END AS Percentage
FROM
Tbl

Upvotes: 0

Related Questions