BBampaa

Lessons › SQL › Chapter 8: Text, dates and safe maths

SQL lesson

Safe maths: empty cells, zero and whole numbers

Three things quietly break calculations. An empty cell makes the whole sum empty: COALESCE fills it with 0. Dividing by zero stops the query: NULLIF(x, 0) turns a 0 into an empty cell, so the answer is just empty. And whole numbers divided by whole numbers drop the decimals: 3 / 4 gives 0. Multiplying by 1.0 first keeps them.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Rates that don't break

SELECT order_id, price + COALESCE(shipping, 0) AS total
FROM orders

Result:

order_idtotal
O200175
O200215
O200327
O200435

Step by step

  1. Anything plus an empty cell is empty. COALESCE uses 0 instead.
    SELECT order_id, price + COALESCE(shipping, 0) AS total FROM orders
  2. Whole number ÷ whole number drops the decimals. 1.0 * keeps them.
    SELECT product_id, 1.0 * returned / sold AS return_rate FROM products
  3. Dividing by 0 stops the query. NULLIF(sold, 0) gives an empty answer instead.
    SELECT product_id, 1.0 * returned / NULLIF(sold, 0) AS return_rate FROM products

Practice

Try each one in your head first, then open the answer.

1. Using orders, show order_id and each order's total: price plus shipping, where shipping can be empty. Name it total.
SELECT order_id, price + COALESCE(shipping, 0) AS total FROM orders
2. Using invoices, show invoice_id and each invoice's total: item_cost plus delivery_fee, where delivery_fee can be empty. Name it total.
SELECT invoice_id, item_cost + COALESCE(delivery_fee, 0) AS total FROM invoices

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed