139

I'd like to perform division in a SELECT clause. When I join some tables and use aggregate function I often have either null or zero values as the dividers. As for now I only come up with this method of avoiding the division by zero and null values. 

(CASE(COALESCE(COUNT(column_name),1)) WHEN 0 THEN 1
ELSE (COALESCE(COUNT(column_name),1)) END)

I wonder if there is a better way of doing this?

Asclepius
  • 57,944
  • 17
  • 167
  • 143
William Wino
  • 3,599
  • 7
  • 38
  • 61

6 Answers6

361

You can use NULLIF function e.g.

something/NULLIF(column_name,0)

If the value of column_name is 0 - result of entire expression will be NULL

TmTron
  • 17,012
  • 10
  • 94
  • 142
Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
  • 26
    something like value/COALESCE(NULLIF(column_name,0), 1) would work I suppose – Akash Jul 16 '13 at 17:57
  • Tried it with COALESCE as @Akash suggested and it did the work – a.b.d Apr 23 '14 at 23:10
  • 6
    This is the perfectly semantic solution to many division by zero problems! Sometimes you don't want the division to be some other value, you want it not to be computed at all! – LeoRochael Aug 27 '18 at 15:40
67

Since count() never returns NULL (unlike other aggregate functions), you only have to catch the 0 case (which is the only problematic case anyway). So, your query simplified:

CASE count(column_name)
   WHEN 0 THEN 1
   ELSE count(column_name)
END

Or simpler, yet, with NULLIF(), like Yuriy provided.

Quoting the manual about aggregate functions:

It should be noted that except for count, these functions return a null value when no rows are selected.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
60

I realize this is an old question, but another solution would be to make use of the greatest function:

greatest( count(column_name), 1 )  -- NULL and 0 are valid argument values

Note: My preference would be to either return a NULL, as in Erwin and Yuriy's answer, or to solve this logically by detecting the value is 0 before the division operation, and returning 0. Otherwise, the data may be misrepresented by using 1.

vol7ron
  • 40,809
  • 21
  • 119
  • 172
  • I'm going to use this, since my divisor is an "elapsed time" for a process, and 0 probably means a fraction of a second, so I'll use 0.01 minutes as a default time. I'm only comparing process performance. – PhilHibbs Jan 05 '17 at 14:42
  • 1
    This was the simplest solution to me, in terms of readability – Florent Destremau Sep 10 '19 at 15:54
  • I do think more people need to read your note. I've come across some code which put in place variants of the solutions presented here without further thought, and dividing by 1 has very significant implications for certain contexts. Even the original question could have been frame challenged since it's almost certain their application shouldn't have been receiving a `count / 1` output when the denominator had no results. – bsplosion Jul 07 '23 at 15:56
10

Another solution avoiding division by zero, replacing to 1

select column + (column = 0)::integer;
Manvel
  • 750
  • 7
  • 10
  • This is really clever. – lightyrs Feb 28 '19 at 20:58
  • 1
    Worked for me! can you please explain what it does? – amrakm May 20 '22 at 15:44
  • I believe `(column = 0)` returns a boolean. When a boolean is cast to an integer, `TRUE` becomes `1` and FALSE becomes `0`. So When column=0, this returns 1, NULL returns NULL, 1 returns 1, any other int returns itself. Casting is usually slow, and this code is really hard to understand though. – Chris Dec 21 '22 at 09:08
3

If you want the divider to be 1 when the count is zero:

count(column_name) + 1 * (count(column_name) = 0)::integer

The cast from true to integer is 1.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
0

I did the below as suggested and it worked

column_name / COALESCE(NULLIF(column_name,0), 1)

Even a sum function can work like below:

 (
sum(column_name) :: decimal / COALESCE(
  NULLIF(sum(other_column_name) :: decimal, 0),1) :: decimal * 100
)
Cyber
  • 2,194
  • 4
  • 22
  • 41