Numbers: INT64, FLOAT64 and NUMERIC

Prices, quantities and rates are numbers, but BigQuery keeps them in three types, and a wrong pick gives an answer that is a unit or a cent off without raising an error. This lesson uses two tables of learn.shop, the online shop from the time zones lesson: products has a list price for each product, and order_items has one row per line of an order, with the quantity bought and the unit_price paid.

How it works

INT64 holds whole numbers. NUMERIC holds decimals exactly, with up to 9 digits after the point, which suits money. FLOAT64 holds a decimal as the nearest binary fraction, so it covers a huge range but is approximate. In learn.shop, quantity is an INT64, and price and unit_price are NUMERIC. The type of a result follows the types that go in, and some functions change it, so check the type of a number before you trust a total.

Common mistake

CAST(19.99 AS INT64) is 20, because the cast rounds. A point count or a box count built this way is one too high whenever the fraction is a half or more. The query runs and the totals look close. Cut with TRUNC or DIV first, then cast when the column must be an INT64.

Worked example

SELECT order_id, product_id, quantity, unit_price,
  unit_price * quantity * 0.075 AS tax_unrounded,
  ROUND(unit_price * quantity * 0.075, 2) AS tax_numeric,
  ROUND(CAST(unit_price AS FLOAT64) * quantity * 0.075, 2) AS tax_float
FROM `learn.shop.order_items`
WHERE order_id IN (1, 72)
ORDER BY order_id

A sales tax of 7.5% is worked out on each line and rounded to the cent. Both lines are 4 units. Before rounding the tax is 7.281 on the first and 5.985 on the second, and tax_unrounded is a NUMERIC.

The first line gives 7.28 in both types. The second is exactly half a cent, so NUMERIC rounds it up to 5.99. The FLOAT64 copy of 19.95 is a little under 19.95, so its tax falls a hair below the half cent and rounds down to 5.98. The query raises no error.

All BigQuery lessons