Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with:

SQL ISZERO() And NULLIF() For Dividing By Zero

By Ben Nadel on
Tags: SQL

I am working on writing a bunch of SQL reports that have a lot of SQL aggregates. One of the computations that comes up a lot is the figuring out of percentages. To this, as you all know, you have to divide one number by another number and of course the number on the bottom cannot be Zero.

Since many of these bottom numbers are aggregates (thing SQL SUM(), MAX(), MIN()), I don't want to call them too often. What I want is something that works like the SQL function ISNULL(), where you can pass in a value just once. So, I wrote a quick little function called ISZERO():

  • CREATE FUNCTION IsZero (
  • @Number FLOAT,
  • @IsZeroNumber FLOAT
  • )
  • RETURNS FLOAT
  • AS
  • BEGIN
  •  
  • IF (@Number = 0)
  • BEGIN
  • SET @Number = @IsZeroNumber
  • END
  •  
  • RETURN (@Number)
  •  
  • END

This simply checks the passed in number to see if it Zero. If it is, then I just pass back the alternate number. If is not Zero, then I pass back the original value. This allows me to pass in computational-heavy numbers without having to compute them more than once.

  • (
  • CAST( SUM( r.price ) AS FLOAT ) /
  • (
  • SELECT
  • dbo.ISZERO( SUM( r2.price ), 1 )
  • FROM
  • reward r2
  • INNER JOIN
  • [order] o2
  • ON
  • r2.order_id = o2.id
  • WHERE
  • o2.user_id = u.id
  • ) *
  • 100
  • ) AS percentage_used

Notice that I only have to run the SUM() aggregate once (for each value).

What's also nice about this is that it converts the passed-in numbers to FLOAT automatically which saves me having to do it on the other end of things. This is great as all of the reporting values require FLOAT values.

After I wrote this, it occurred to me that this is probably a VERY common problem. So, I did some Google searches to see how other people have dealt with this. Doing so, I came across a very cool function named NULLIF(). This function returns a NULL if the two passed-in arguments are the same value. That can be super useful.

You could accomplish the ISZERO() method using NULLIF() this way:

  • (
  • CAST( SUM( r.price ) AS FLOAT ) /
  • ISNULL(
  • NULLIF(
  • (
  • SELECT
  • SUM( r2.price )
  • FROM
  • reward r2
  • INNER JOIN
  • [order] o2
  • ON
  • r2.order_id = o2.id
  • WHERE
  • o2.user_id = u.id
  • ),
  • 0
  • ),
  • 1
  • ) *
  • 100
  • ) AS percentage_used

This works fine, but is just a bit too wordy for my taste. But certainly, NULLIF() is a great function to have under the belt.

Tweet This Groovy post by @BenNadel - SQL ISZERO() And NULLIF() For Dividing By Zero Thanks my man — you rock the party that rocks the body!


Reader Comments

I do it the lazy mans way:

CASE when denominator = 0
then 0
else numerator/denominator end as result

Did I use those terms correctly? I suck at math, but I think you get the picture.

Or, you can take the REALLY lazy way out:

<cfif not isNumeric(qryGraphData_total.t_avg)>
<cfset t_avg = 0>
<cfelse>
<cfset t_avg = qryGraphData_total.t_avg>
</cfif>

Or, you can take the REALLY lazy way out:

<cfif not isNumeric(qryGraphData_total.t_avg)>
<cfset t_avg = 0>
<cfelse>
<cfset t_avg = qryGraphData_total.t_avg>
</cfif>

@Todd,

That is how I would do it if the value that I was testing was a simple value, but in my case, I was testing an aggregate which has a lot of processing to it:

CASE
WHEN SUM( r.price ) = 0
THEN 1
ELSE SUM( r.price )
END

I would have to run the SUM() aggregate twice which is what I want to avoid doing.

@Peter,

That is how I would do it in ColdFusion, but I needed to do all this in the actual SQL statement as I needed to pass this query result off to a report generator. I could have updated the query after it was executed (which is actually what I was doing originally), but then I felt that I could accomplish the same thing faster by tightening up my SQL.