Skip to content

Computed Columns and Divide by Zero

February 7, 2012

Edit: This was a poor example of using the divide by zero handling. I was trying to alter something I’d done in the past and this didn’t work well. I will rewrite this soon with a better example.

I have a few posts on computer columns, the basics of computed columns, using CASE in a computed column, and UDFs in computed columns, but there was another use that someone pointed out to me recently: catching divide by zero errors.

Suppose you have a column that is determining a percentage of profit for some sales. I’ll create a table and include some values:

CREATE TABLE MySales
( salesid int , Product VARCHAR(20) , Cost numeric(10,4) , Price numeric(10,4) , profit AS (price - cost)/ cost
) GO INSERT MySales SELECT 1, 'Bike', 150.45, 180.99
INSERT MySales SELECT 1, 'Shoes', 23.55, 45.99
INSERT MySales SELECT 1, 'Soda', 0.25, .99

If I look at the values in the table, I get back these items:

/*------------------------
SELECT Product
, Cost
, Profit
 FROM MySales
------------------------*/
Product              Cost                                    Profit
-------------------- --------------------------------------- ---------------------------------------
Bike                 150.4500                                0.202991026919242
Shoes                23.5500                                 0.952866242038216
Soda                 0.2500                                  2.960000000000000

Things work great, and everything seems to be fine. However, what if we get some free products that we can sell, say something we made out of scraps, or were given to us as a gift with essentially no cost?

INSERT MySales SELECT 1, 'Keychain', 0, .75

It works. However if I select data:

SELECT Product
, Cost
, Profit
 FROM MySales

I get this:

Product              Cost                                    Profit
-------------------- --------------------------------------- ---------------------------------------
Bike                 150.4500                                0.202991026919242
Shoes                23.5500                                 0.952866242038216
Soda                 0.2500                                  2.960000000000000
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

The computation occurs on query, and it doesn’t work well.

However we can fix this with a function in our computed column. There are a couple choices here. I can use ISNULL and a CASE to build a formula, I could use COALESCE, or I could use NULLIF.

NULLIF returns null if two values are equal. If a NULL is acceptable in my table, I could use that. I could end up with:

CREATE TABLE MySales
( salesid int , Product VARCHAR(20) , Cost numeric(10,4) , Price numeric(10,4) , profit AS (price - cost) / NULLIF(cost ,0) ) GO INSERT MySales SELECT 1, 'Bike', 150.45, 180.99
INSERT MySales SELECT 1, 'Shoes', 23.55, 45.99
INSERT MySales SELECT 1, 'Soda', 0.25, .99
INSERT MySales SELECT 1, 'Keychain', 0, .75
GO SELECT Product
, Profit
 FROM MySales

and get back:

Product              Profit

——————– —————————————

Bike                 0.202991026919242

Shoes                0.952866242038216

Soda                 2.960000000000000

Keychain             NULL

NULL is a good marker here, but your application needs to handle this and let the user know there is an issue.

You could use COALESCE, which returns the first non-null value. I could see any of these as being a valid formula:

, profit AS (price - cost) / COALESCE(cost ,0) 

which uses “0” profit margin as a marker, or even something like:

, profit AS (price - cost) / COALESCE(cost , 999)

which uses 999. Lots of times we’ve used a large, nonsense number as a marker that lets someone know there is a strange value here.

If we converted this to a varchar, it’s possible that we could even use words in there, but I wouldn’t recommend that as downstream uses of this column might involve other calculations.

That’s a short look at how a computed column can solve an easy, common issue: divide by zero.

Credit to Atif Shehzad, whose article I found while researching this.

About these ads

From → Blog

6 Comments
  1. Tom Thomson permalink

    I guess “coalesce(cost, 0)” and “coalesce(cost,999)” are slips of the pen, maybe you meant “coalesce(nullif(cost,0),999) for the latter but what you were thinking of for the former I can’t imagine.

    • These were two different examples, not one. The point I was trying to make clear is that you might choose one of these rules, not necessarily both. This wasn’t code to be used, but code to illustrate that you can use the code different ways if you have different business rules.

  2. Tom.Thomson permalink

    Well, it seems to me that

    [quote], profit AS (price – cost) / COALESCE(cost ,0)

    which uses “0” profit margin as a marker, or even something like:[/quote]
    is wrong because it doesn’t use 0 as a marker; if cost is 0 it still produces a zero divide, and if cost is null, and if cost is null it tries to divide null by 0; in neither case can this result in 0, which the phrase beginning “uses” indicates was the intention.
    For the 999 example, well, it will deliver 999 whenever the price is 1000 times the (non-zero and non-null) cost, and will not deliver 999 if the cost is null or unknown, so it’s hardly using 999 as a marker.
    Each of these examples leaves the divide by zero issue untouched, and since they both deal with the case where cost is null but not with the case where profit is null as a result of a divide by zero error which is what I believe, from the flow of the text, was what these examples were supposed to address.

  3. Ah, I see it now. I missed the divide in there, thinking my cost was being returned. I’ll have to clean up the example to show the use of NULL handling without arithmetic errors.

  4. FearNaught permalink

    Excuse me if I am wrong but surely the profit will be 100% if the cost is zero. So in the example the price is 0.75 and the profit value is 0.75, therefore there is no reason to use NULL or any other marker figure. I would therefore use a CASE state to determine if cost was zero (it should never be NULL of you create the column with NOT NULL and a default value of zero) then profit is 100.

  5. Nope, you’re right. I was trying to build an example based on something similar I’d done, and I didn’t do a good job. This wasn’t the place to use the zero handling.

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 4,301 other followers

%d bloggers like this: