Numbers, Rounding & Money

Numeric functions are how SQL turns raw amounts into report-ready numbers: rounded prices, capped discounts, percentages, remainders, powers, and safe averages. The traps are familiar by now: the query can run and still return a plausible number that is mathematically wrong for the report.

This lesson finishes the Section 6 pattern. Text functions shaped strings, date functions shaped moments, and now numeric functions shape money and ratios without giving up type honesty.

The everyday numeric toolkit

Start with the function vocabulary. These all return one value for the row, just like the text and date functions you just used:

SELECT ROUND(12.345, 2) AS rounded,
       CEIL(12.345) AS ceiling,
       FLOOR(12.345) AS floor_value,
       TRUNC(12.345, 2) AS truncated,
       ABS(-12.345) AS absolute_value,
       MOD(17, 5) AS remainder,
       POWER(2, 5) AS power_value,
       GREATEST(3, 17, 9) AS greatest_value,
       LEAST(3, 17, 9) AS least_value;

 rounded | ceiling | floor_value | truncated | absolute_value | remainder | power_value | greatest_value | least_value
---------+---------+-------------+-----------+----------------+-----------+-------------+----------------+-------------
   12.35 |      13 |          12 |     12.34 |         12.345 |         2 |          32 |             17 |           3
(1 row)

ROUND(x, n) rounds to n decimal places. CEIL moves up to the next whole number; FLOOR moves down. TRUNC(x, n) cuts off after n decimal places without rounding. ABS removes the sign, MOD gives the remainder, and POWER raises one number to another. GREATEST and LEAST are useful for caps and floors: keep a discount under a maximum, keep a score above zero, or clamp a value into a business range.

That first column, ROUND(12.345, 2), is the spelling Section 13 will use inside percentage calculations. Learn it now as "round this numeric expression to this many displayed decimal places."

Do not treat these as cosmetic functions only. CEIL can decide how many boxes you need when each box holds a fixed quantity. MOD can separate every fifth ticket for an audit sample. GREATEST can keep a fee from going below zero after a credit is applied. The function is small; the business rule it encodes is often not.

Rounding has rules

Rounding is not just "make it shorter." It has a tie-breaking rule for values exactly halfway between two results:

SELECT ROUND(CAST(2.5 AS numeric)) AS round_2_5,
       ROUND(CAST(3.5 AS numeric)) AS round_3_5,
       ROUND(CAST(-2.5 AS numeric)) AS round_neg_2_5,
       ROUND(CAST(2.675 AS numeric), 2) AS round_two_places;

 round_2_5 | round_3_5 | round_neg_2_5 | round_two_places
-----------+-----------+---------------+------------------
         3 |         4 |            -3 |             2.68
(1 row)

For Postgres numeric, these half values move away from zero. That is not the only rounding convention in the world. You will hear about "banker's rounding," where exact halves go to the nearest even result, and you will see different rules in different databases, languages, and spreadsheet tools.

The professional habit is not to memorize folklore. If cents, tax, payouts, or grades depend on tie behavior, test the exact engine and type you are using, then write the rule into the report spec.

This is also why you should be suspicious of moving a rounded number between tools and expecting it to stay identical. SQL, app code, and spreadsheets can all look like they are "rounding to two places" while disagreeing on exact halfway cases. The closer the number is to money or compliance, the less acceptable that hidden disagreement becomes.

Money belongs in NUMERIC

Prices in the course datasets use NUMERIC, not floating point. The reason is the same one 3.4 previewed: floating-point types are approximate, and money-like values need exact decimal behavior.

SELECT CAST(0.10 AS double precision) + CAST(0.20 AS double precision) AS float_total,
       CAST(0.10 AS numeric) + CAST(0.20 AS numeric) AS numeric_total;
 
SELECT line_id,
       quantity,
       unit_price,
       discount_pct,
       ROUND(quantity * unit_price * (100 - discount_pct) / 100, 2) AS line_total
FROM cafe_sales
WHERE discount_pct > 0
ORDER BY line_id
LIMIT 5;

     float_total     | numeric_total
---------------------+---------------
 0.30000000000000004 |          0.30
(1 row)
 line_id | quantity | unit_price | discount_pct | line_total
---------+----------+------------+--------------+------------
       7 |        1 |       4.50 |           10 |       4.05
       8 |        1 |       8.25 |           10 |       7.43
      22 |        1 |       4.00 |           15 |       3.40
      23 |        2 |       7.50 |           15 |      12.75
      24 |        2 |       3.80 |           15 |       6.46
(5 rows)

The second grid is the money pattern: keep inputs exact, do the arithmetic as NUMERIC, and round the final display value to cents. Rounding each intermediate step is a business rule, not a default habit. If the business says "round each line, then sum," model that. If it says "sum exact values, then round the invoice," model that instead.

That last sentence matters because both policies can be legitimate, and they can differ by cents. SQL will do exactly what you ask. Your job is to make the report's sentence precise enough that the arithmetic follows it.

Percentages without integer division

Ratios are where old numeric traps come back wearing business clothes. The cafe_pos extract has line rows, some with a discount. The wrong version uses integer arithmetic for the percentage:

SELECT COUNT(*) AS sale_lines,
       COUNT(*) FILTER (WHERE discount_pct > 0) AS discounted_lines,
       COUNT(*) FILTER (WHERE discount_pct > 0) * 100 / COUNT(*) AS integer_pct,
       ROUND(
         100 * CAST(COUNT(*) FILTER (WHERE discount_pct > 0) AS numeric)
         / NULLIF(COUNT(*), 0),
         1
       ) AS numeric_pct
FROM cafe_sales;

 sale_lines | discounted_lines | integer_pct | numeric_pct
------------+------------------+-------------+-------------
         90 |               15 |          16 |        16.7
(1 row)

The grid shows the bug and the fix side by side. integer_pct throws away the fraction because the inputs are integer counts. numeric_pct casts the numerator to numeric, protects the denominator with NULLIF, and uses ROUND(x, 1) for the final display.

This is the percentage ritual: make the decimal type explicit before division, protect the denominator, then round for humans at the end.

The order is deliberate. If you round before dividing, you can lose the signal. If you divide integers, you can lose the fraction. If you forget the zero-denominator case, the report can fail on the slow day when a filter matches nothing. A ratio query is small enough to type quickly, but important enough to type carefully.

Division by zero: NULLIF keeps the query alive

Back in the NULL lesson, NULLIF was promised for division protection. The shape is total / NULLIF(count, 0): if the count is zero, turn it into NULL before division.

Without it, the query fails:

SELECT branch,
       total,
       sale_count,
       total / sale_count AS average_sale
FROM branch_totals
ORDER BY branch;

ERROR:  division by zero

With it, the zero-count branch becomes an honest NULL average:

SELECT branch,
       total,
       sale_count,
       ROUND(total / NULLIF(sale_count, 0), 2) AS average_sale
FROM branch_totals
ORDER BY branch;

 branch | total  | sale_count | average_sale
--------+--------+------------+--------------
 Campus |  72.50 |          4 |        18.13
 Center | 125.00 |          5 |        25.00
 Harbor |   0.00 |          0 |         NULL
(3 rows)

That NULL is not a failure. It is the database saying, "there is no average sale when there were zero sales." If the report needs a display value, use COALESCE at the outer edge where a human label belongs. Keep the arithmetic honest first.

You now have the Section 6 shaping toolkit: text, time, and numeric values can all be transformed safely inside a query. Section 7 changes the scale of the questions: subqueries let one query use the answer from another query.