Null-safe order totals with discounts and shipping

Question

The checkout table leaves `discount` and `shipping_fee` as `NULL` when they do not apply, which silently breaks arithmetic totals.

From `checkout_lines`, return `line_id`, `gross_amount`, `discount_applied` (0 when missing), `shipping_charged` (0 when missing) and `net_payable` = gross − discount +

Accepted Answer

Step 1: Remember the core rule: any arithmetic involving `NULL` returns `NULL`, so `gross_amount - discount` would wipe out valid totals. Step 2: Wrap each optional numeric column in `COALESCE(col, 0)` to substitute a neutral value. Step 3: Build `net_payable` from those coalesced expressions so every row produces a real number. Step 4: `COALESCE` works on text too — `COALESCE(coupon_code, 'No coupon')` gives the reporting label. Step 5: Project the requested columns with clear aliases.

Null-safe order totals with discounts and shipping

More NULL / NOT NULL Handling questions

Asked at Zomato