# division by zero: X / 0

> The / operator fails the whole query when one row has a divisor of 0. The message shows the numbers of the failing row. SAFE_DIVIDE and NULLIF turn the failure into a NULL, IEEE_DIVIDE returns infinity, and a WHERE on the divisor drops the row.

Source: https://docs.querylab.io/bigquery-errors/division-by-zero/

Updated: 2026-10-30

```text
division by zero: 20 / 0
```

## What BigQuery means

One row had a divisor of 0, and BigQuery does not guess what the result should be. It stops the whole query, so one bad row hides all the good ones. This is a runtime error. The query compiles and the dry run passes, because BigQuery cannot know the data until it reads it.

The numbers in the message are the values of the failing row, not the text of your expression. `20 / 0` means a row where the left side was 20 and the right side was 0. It does not name the column or give a position, so you search for the division yourself.

In a script, the message starts with `Query error:` and ends with the position of the statement that failed, not of the division: `Query error: division by zero: 10 / 0 at [2:1]`.

## The smallest query that reproduces it

```sql
WITH t AS (SELECT 10 AS a, 0 AS b)
SELECT a / b AS ratio
FROM t
```

```text
division by zero: 10 / 0
```

A divisor that is a constant 0 fails the same way: `SELECT 1 / 0` gives `division by zero: 1 / 0`.

## The fixes

Take this table, where one row has `b = 0`:

```sql
WITH t AS (
  SELECT 10 AS a, 2 AS b
  UNION ALL SELECT 20, 0
  UNION ALL SELECT 30, 5
)
SELECT a / b AS ratio
FROM t
```

```text
division by zero: 20 / 0
```

**Find the rows first.** The query below returns them:

```sql
SELECT a, b
FROM t
WHERE b = 0
```

**`SAFE_DIVIDE` returns NULL for those rows.** The other rows keep their values:

```sql
SELECT a, SAFE_DIVIDE(a, b) AS ratio
FROM t
ORDER BY a
```

The ratio is `5.0`, `NULL` and `6.0`.

**`NULLIF` does the same with the operator.** `NULLIF(b, 0)` turns a zero into NULL, and dividing by NULL gives NULL:

```sql
SELECT a, a / NULLIF(b, 0) AS ratio
FROM t
ORDER BY a
```

Use `NULLIF` when the divisor is a long expression and you want the division to stay readable.

**Show 0 instead of NULL.** Wrap the result in `COALESCE`:

```sql
SELECT a, COALESCE(SAFE_DIVIDE(a, b), 0) AS ratio
FROM t
ORDER BY a
```

Be careful with this one. A ratio of 0 and a ratio that does not exist are different facts, and the 0 will be averaged with the real values.

**Leave the rows out.** Filter on the divisor:

```sql
SELECT a, a / b AS ratio
FROM t
WHERE b != 0
ORDER BY a
```

The rows with `b = 0` are gone from the result. `WHERE b != 0 AND a / b > 1` happened to run here, but BigQuery documents that "the order of evaluation of operands to `AND` and `OR` can vary, and evaluation can be skipped if unnecessary." Use `CASE`, `IF` or `SAFE_DIVIDE` instead.

**A conditional expression skips the division.** `CASE`, `IF` and `COALESCE` evaluate left to right and only evaluate the branch they pick:

```sql
WITH t AS (SELECT 10 AS a, 0 AS b)
SELECT CASE WHEN b != 0 THEN a / b END AS ratio
FROM t
```

**Aggregates divide too.** `SUM(a) / SUM(b)` fails when the sum in the divisor is 0:

```sql
WITH t AS (SELECT 10 AS a, 0 AS b)
SELECT SUM(a) / SUM(b) AS ratio
FROM t
```

```text
division by zero: 10 / 0
```

```sql
WITH t AS (SELECT 10 AS a, 0 AS b)
SELECT SAFE_DIVIDE(SUM(a), SUM(b)) AS ratio
FROM t
```

## INT64 and FLOAT64

The type of the numbers makes no difference. Dividing by `0` and by `0.0` both fail, and so does NUMERIC. `SELECT 10.5 / 0.0` gives `division by zero: 10.5 / 0`. The message prints the divisor as `0` in both cases.

Other languages return infinity for a float divided by zero. BigQuery does that only through `IEEE_DIVIDE`:

```sql
SELECT a, IEEE_DIVIDE(a, b) AS ratio
FROM t
ORDER BY a
```

This returns `5.0`, `Infinity` and `6.0`. `IEEE_DIVIDE(-10, 0)` is `-Infinity` and `IEEE_DIVIDE(0, 0)` is `NaN`. Infinity and NaN are real FLOAT64 values that move through sums and comparisons, so use it when you want IEEE behavior and not as a quiet replacement for NULL.

## The variants of the message

Each line was produced by BigQuery:

```text
division by zero: 20 / 0
division by zero: 10.5 / 0
division by zero: MOD(10, 0)
Query error: division by zero: 10 / 0 at [2:1]
```

`DIV(10, 0)`, the integer division function, gives `division by zero: 10 / 0`, worded like the operator. `MOD(10, 0)` gives `division by zero: MOD(10, 0)`. For both, `SAFE.DIV(10, 0)` and `SAFE.MOD(10, 0)` return NULL. `SAFE_DIVIDE` does not replace `MOD`.

The `Query error:` form comes from a script whose second statement fails:

```sql
CREATE TEMP TABLE t AS SELECT 10 AS a, 0 AS b;
SELECT a / b AS ratio FROM t;
```

## Queries that look wrong but run

**A NULL divisor.** `SELECT 10 / CAST(NULL AS INT64)` returns NULL. Only a real 0 fails.

**A division in a query with no rows.** `WHERE FALSE` or an empty table never evaluates the division, so nothing fails:

```sql
WITH t AS (SELECT 10 AS a, 0 AS b)
SELECT a / b AS ratio
FROM t
WHERE FALSE
```

**A zero numerator.** `SELECT 0 / 5` returns `0.0`. Only the divisor matters.

## Different errors that look like this one

**`double overflow: 1e+308 / 1e-308`.** The divisor is not 0 but tiny, and the result does not fit in a FLOAT64:

```sql
WITH t AS (SELECT 1e308 AS a, 1e-308 AS b)
SELECT a / b AS ratio
FROM t
```

`SAFE_DIVIDE(a, b)` returns NULL for this query.

## In Querylab.io

The editor does not mark this error, because it depends on the data. It comes back from BigQuery when the query runs.

After a failed run of a single query, the status bar offers fixes when the query has one division that can fail:

- `Return NULL when dividing by zero` rewrites `a / b` to `SAFE_DIVIDE(a, b)`. When the division is in a condition such as `WHERE`, the button reads `Skip rows where the divisor is 0 (SAFE_DIVIDE)`.
- `Show rows where the divisor is 0` runs a query that lists them.
- `Skip rows where the divisor is 0` adds `(b IS NULL OR b != 0)` to the `WHERE`.

The last two appear for a plain row-by-row `SELECT`, without `GROUP BY`, aggregates or window functions, and with the division in the select list. No fix is offered for `MOD`, for an error from inside a script, or when the query reads a view or calls a stored function, because the division that failed may be hidden there.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Operators, arithmetic operators](https://cloud.google.com/bigquery/docs/reference/standard-sql/operators)

- [Mathematical functions, SAFE_DIVIDE and IEEE_DIVIDE](https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions)

- [Conditional expressions](https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions)