0

I have the following code as part of a Script:

ROUND
(
  (
    (COUNT(DISTINCT CASE WHEN ONLINE_SALES > 0 THEN CONTACT_KEY ELSE NULL END)) 
    / 
    (COUNT(DISTINCT CASE WHEN ONLINE_SALES > 0 OR OFFLINE_SALES > 0 THEN CONTACT_KEY ELSE NULL END))
  ),3
) AS UNIQ_ONLINE_SHOP_RATE

when I run the script I get the 'Divizer is equal to zero' erro.

I ran the denominator and numerator separately which both equal zero so I understand the error.

I have tried NULLiF(,0) as so:

ROUND
(
  (
    (
      COUNT(DISTINCT CASE WHEN ONLINE_SALES > 0 THEN CONTACT_KEY ELSE NULL END) / 
      nullif((COUNT(DISTINCT CASE WHEN ONLINE_SALES > 0 OR OFFLINE_SALES > 0 THEN CONTACT_KEY ELSE NULL END)),0)
    ),3
  ) AS UNIQ_ONLINE_SHOP_RATE

but then get 'FROM keyword not found where expected error.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
Kaz111
  • 9
  • 4
  • You might be missing **ROUND((** from the second expression. – Jason Seek Well Dec 21 '22 at 19:39
  • Just forgot to paste it but issue still the same, thank you for looking though :) – Kaz111 Dec 21 '22 at 19:49
  • 1
    I'm counting 7 open parentheses and only 6 closing ones. Try this instead ```ROUND(COUNT(DISTINCT CASE WHEN ONLINE_SALES > 0 THEN CONTACT_KEY ELSE NULL END)/ nullif(COUNT(DISTINCT CASE WHEN ONLINE_SALES > 0 OR OFFLINE_SALES > 0 THEN CONTACT_KEY ELSE NULL END),0),3) AS UNIQ_ONLINE_SHOP_RATE``` – Nicko Dec 21 '22 at 19:52
  • So, your divisor is zero, what does that mean? It means there are no cases where online_sales or offline_sales are positive. Since your numerator is the same thing for *only* online_sales, then your numerator is also 0 for that case. A very easy way to handle that then is to say NULLIF(...., 1) [not 0] and then you should get zero when the division occurs. – Chris Maurer Dec 21 '22 at 20:21
  • As @Nicko said, this is just a typo as you have too many opening braces in your final query which is why you get the syntax error. Remove one of the opening braces and it should work. – MT0 Dec 21 '22 at 21:35

2 Answers2

0

Here are 3 options (CASE, NULLIF and DECODE), visually simple to understand - use a CTE to calculate both values, while the main query divides them, taking care about 0 as the denominator:

with temp as
  (select
     COUNT(DISTINCT CASE WHEN ONLINE_SALES > 0                      THEN CONTACT_KEY ELSE NULL END) val1,
     COUNT(DISTINCT CASE WHEN ONLINE_SALES > 0 OR OFFLINE_SALES > 0 THEN CONTACT_KEY ELSE NULL END) val2
   from your_table
  )
select 
  -- using CASE
  val1 / case when val2 = 0 then null else val2 end  as result_1,
  -- using NULLIF
  val1 / nullif(val2, 0)                             as result_2
  -- using DECODE
  val1 / decode(val2, 0, null, val2)                 as result_3  
from temp;

Shouldn't be difficult to round the result at the end.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • 2
    "*This is Oracle, and there's no `NULLIF`*" - I see it different: [NULLIF](https://docs.oracle.com/database/121/SQLRF/functions128.htm#SQLRF00681), I think it exists since Oracle 9i – Wernfried Domscheit Dec 21 '22 at 21:28
  • 1
    OMG, @Wernfried! I've been around for ages and didn't even know NULLIF exists in Oracle. What a shame! I should've - at least - checked it myself first. So sorry and - thank you for the info. – Littlefoot Dec 21 '22 at 21:55
0

Is this your real query? I assume ONLINE_SALES > 0 or OFFLINE_SALES > 0 is the default and ONLINE_SALES = 0 or OFFLINE_SALES = 0 is the exception.

Then in most cases your query would result in

COUNT(DISTINCT CONTACT_KEY) / COUNT(DISTINCT CONTACT_KEY)

which is not so "exciting", i.e. always 1

With some best-guess I would do:

NULLIF(
   ROUND( 
      COUNT(DISTINCT CONTACT_KEY) 
      / 
      NULLIF(COUNT(DISTINCT ONLINE_SALES), 0) 
   , 3)
, 0) AS UNIQ_ONLINE_SHOP_RATE
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110