In this lesson, you will learn how to handle financial data safely to prevent "penny-drift" and rounding errors in your SQL queries.
The Danger of Floating-Point Math
When you perform arithmetic in databases, the way the computer stores numbers matters immensely. You might be tempted to use types like FLOAT or REAL because they are flexible, but these types use binary approximations. In finance, this is dangerous; an calculation like 0.1 + 0.2 might result in 0.30000000000000004 instead of 0.3. Over thousands of transactions, those tiny discrepancies add up to massive accounting errors.
Using Fixed-Point Arithmetic
To maintain absolute accuracy, we use fixed-point types, typically called DECIMAL or NUMERIC. When you define these, you specify the precision (total number of digits) and the scale (number of digits after the decimal point).
For example, DECIMAL(19, 4) tells the database to store a number with up to 19 digits total, with 4 of those always reserved for decimals. This ensures that $1.00 is stored exactly as 1.0000 every single time, without binary approximation.
Managing Rounding in Queries
Even with fixed-point math, you often need to summarize data—such as calculating a tax or a discount. When you perform division, you might end up with more decimal places than your currency supports. You should explicitly use rounding functions to control this, rather than letting the database truncate or round unexpectedly:
SELECT
product_name,
ROUND(price * 0.0825, 2) AS tax_amount
FROM sales;
In this query, ROUND(..., 2) ensures that the final tax calculation is trimmed to two decimal places, matching standard currency formats. By combining DECIMAL types for storage and ROUND() functions for calculations, you ensure that your financial reports remain consistent and audit-ready. Always avoid FLOAT for money, and always be intentional about where and how you round your results during the transformation phase.
Key Takeaway: To ensure financial accuracy, always use DECIMAL or NUMERIC data types and explicitly manage rounding to match your currency’s smallest unit.