Numbers, Rounding and Ratios

Percentages that are actually correct: integer division, ROUND, NULLIF and safe averages.

• Intermediate
• 12 min read
• 3 objectives

Before this lessonLesson 9: Dates and Times

Most "wrong number" bugs in SQL come from three places: integer division, rounding at the wrong moment, and dividing by zero. Each has a one-line fix.

Integer division truncates

When both sides are integers, many engines do integer division and throw the remainder away. Multiply by 1.0, or CAST, to force a real division.

SELECT 7 / 2          AS integer_division,
       7 * 1.0 / 2    AS real_division,
       CAST(7 AS REAL) / 2 AS cast_division,
       7 % 2          AS remainder;

integer_division | real_division | cast_division | remainder
-----------------+--------------+--------------+----------
3                | 3.5           | 3.5           | 1

ROUND, and where to put it

Round once, at the end. Rounding inputs before aggregating them compounds the error across every row.

SELECT ROUND(AVG(total), 2) AS avg_rounded_once,
       AVG(ROUND(total, 0))  AS avg_of_rounded
FROM orders;

avg_rounded_once | avg_of_rounded
-----------------+------------------
78.5             | 78.66666666666667

SELECT ABS(-12.7) AS absolute,
       ROUND(12.3456, 2) AS two_places,
       CAST(12.9 AS INTEGER) AS truncated,
       MAX(3, 9) AS bigger,
       MIN(3, 9) AS smaller;

absolute | two_places | truncated | bigger | smaller
---------+-----------+----------+-------+--------
12.7     | 12.35      | 12        | 9      | 3

Percentages that survive an empty group

The shape below is worth memorising: multiply by 100.0, divide by the count, and wrap the denominator in NULLIF(x, 0) so an empty group yields NULL instead of an error.

SELECT country,
       COUNT(*) AS total,
       SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END) AS approved,
       ROUND(100.0 * SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END)
             / NULLIF(COUNT(*), 0), 2) AS approved_pct
FROM transactions
GROUP BY country;

country | total | approved | approved_pct
--------+------+---------+-------------
DE      | 2     | 1        | 50
US      | 3     | 2        | 66.67

That is the engine of LeetCode 1633 Percentage of Users Attended a Contest and LeetCode 1211 Queries Quality and Percentage — both are this expression with different column names.

AVG skips NULL, and that matters

Aggregates ignore NULL rather than treating it as zero, so AVG divides by the number of non-null rows. When a missing value should count as zero, say so explicitly.

SELECT COUNT(*) AS rows_total,
       COUNT(age) AS ages_present,
       AVG(age) AS avg_ignoring_null,
       AVG(COALESCE(age, 0)) AS avg_treating_null_as_zero
FROM people;

rows_total | ages_present | avg_ignoring_null | avg_treating_null_as_zero
-----------+-------------+------------------+--------------------------
3          | 2            | 32                | 21.333333333333332

Weighted averages

A plain AVG of per-row values is not the same as the overall ratio. When each row carries a different weight, sum the parts and divide the sums.

SELECT ROUND(AVG(total), 2) AS avg_per_order,
       ROUND(SUM(total) / COUNT(DISTINCT customer_id), 2) AS avg_per_customer
FROM orders;

avg_per_order | avg_per_customer
--------------+-----------------
78.5          | 117.75

Key takeaways

• Force real division with * 1.0 or CAST(... AS REAL).
• Round once, after aggregating.
• NULLIF(denominator, 0) turns a crash into a NULL.
• Aggregates skip NULL; use COALESCE when zero is the right answer.

-- Write your solution here