Avoid division by zero: 1 / 0 error in WITH clause

Question

I am using the following in a WITH clause to create a FULL JOIN in Big Query:

    WITH
    a AS(
        SELECT
        date AS Date, 
        SUM(Val1 / (1 - (Val2 + Val3))) AS Calc1,
        FROM `project.dataset.table1`
        GROUP BY Date
        ),
    b as (SELECT
        date AS Date, 
        FROM `project.dataset.table2`
        GROUP BY Date
        )

SELECT a.Date, SUM(Calc1)

FULL JOIN a on b.Date = a.Date

GROUP BY b.Date

Calc1 is creating a 'division by zero: 1 / 0' error, and I can't seem to work out how to restructure this so it doesn't occur. The query works fine outside of the WITH clause, as I can simply not include the GROUP BY so have no need to SUM Calc1?

can you double check your query fragment please - it looks like little mess and no way to be correct - mostly because of `SUM(Calc1)` I think — Mikhail Berlyant, Nov 28 '19 at 16:18
you cannot group by SUM(Calc1) - Aggregate function SUM not allowed in GROUP BY — Mikhail Berlyant, Nov 28 '19 at 16:20
First: `AS Calc1,` ... remove the `,`. Second you can use a `CASE`... if VAL2+VAL3 = 1 you can assume ZERO to sum in Calc1. — Nifriz, Nov 28 '19 at 16:23
Possible duplicate of [How to avoid the "divide by zero" error in SQL?](https://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql) — Amira Bedhiafi, Nov 28 '19 at 16:28
@xXx no - it's not a duplicate since google-bigquery has its own tool to handle "zero division" error. — Stas Buzuluk, Dec 25 '19 at 10:54

Mikhail Berlyant · Answer 1 · 2019-11-28T16:59:21.320

5

Below is for BigQuery Standard SQL

Use

SUM(SAFE_DIVIDE(Val1, 1 - (Val2 + Val3))) AS Calc1

instead of

SUM(Val1 / (1 - (Val2 + Val3))) AS Calc1

edited Nov 28 '19 at 16:59

answered Nov 28 '19 at 16:27

Mikhail Berlyant

165,386
8
154
230

Why would I need SAFE_DIVIDE in addition to IFNULL? – J. Ayo Nov 28 '19 at 16:56
you can omit IFNULL - but you DO really need SAFE_DIVIDE. I will update answer. reason I included it initially is - I thought it might give you an idea of introducing some default value for rows where it is division by 0 – Mikhail Berlyant Nov 28 '19 at 16:58

score 1 · Answer 2 · answered Nov 28 '19 at 16:25

1

Use NULLIF :

WITH
a AS(
SELECT
date AS Date, 
SUM(Val1 / NULLIF((1 - (Val2 + Val3)),0)) AS Calc1,
FROM `project.dataset.table1`
GROUP BY Date, SUM(Calc1)
)

answered Nov 28 '19 at 16:25

Amira Bedhiafi

8,088
6
24
60

Can't work... if VAL2+VAL3 = 1 `NULLIF` return `NULL`... VAL1/NULL = ERROR – Nifriz Nov 28 '19 at 16:29
`VAL1 / NULL` **is not an error**. The result is `NULL`. – Gordon Linoff Nov 28 '19 at 16:31

score 0 · Answer 3 · answered Nov 28 '19 at 16:51

Have you analysed your data within "table1" to ensure that Val1, Val2 & Val3 are consistently populated, or do you have NULL values?

This could be the issue with your subtraction from 1.



WITH
    a AS(
        SELECT
        date AS Date, 
        SUM(Val1 / (1 - (isnull(Val2,0.00) + isnull(Val3,0.00)))) AS Calc1
        FROM `project.dataset.table1`
        GROUP BY Date
        ),
    b as (
            SELECT
            date AS Date, 
            FROM `project.dataset.table2`
            GROUP BY Date
        )

SELECT a.Date, SUM(a.Calc1)
FULL JOIN a on b.Date = a.Date`enter code here`
GROUP BY b.Date

Avoid division by zero: 1 / 0 error in WITH clause

3 Answers3