SQL Numeric & Conversion Functions: Rounding, Casting, Arithmetic and Precision

SQL numeric functions are fundamental to producing accurate calculations, especially when working with money, percentages, measurements, and analytical data. The first decision is choosing the correct numeric type: exact integers such as SMALLINT, INT, and BIGINT are appropriate for counts, identifiers, and quantities; NUMERIC or DECIMAL should be used when decimal values must be stored exactly, particularly for financial data; and REAL, FLOAT, or DOUBLE are approximate binary types better suited to measurements, statistics, and machine-learning features. Decimal fractions such as 0.1 generally cannot be represented exactly in binary floating point, which can produce drift in calculations and unreliable equality comparisons. For financial applications, the presentation recommends exact NUMERIC values or integer minor units such as paise or cents rather than floating-point storage.
Rounding operations must also be chosen deliberately because ROUND, CEIL, FLOOR, and TRUNC perform different operations. ROUND returns the nearest value at a specified decimal scale, while CEIL and FLOOR move toward positive and negative infinity respectively. TRUNC, in contrast, removes digits toward zero, making it different from FLOOR for negative values. The deck also highlights that rounding behaviour can depend on both the database engine and numeric type, including differences between half-up and half-even behaviour. Integer division is another common source of silent errors: dividing two integers can discard the fractional component, so percentage calculations should explicitly force numeric division and protect the denominator with NULLIF. Functions such as MOD, POWER, SQRT, ABS, SIGN, GREATEST, LEAST, and WIDTH_BUCKET extend SQL’s arithmetic capabilities for reporting, bucketing, growth calculations, and statistical analysis.
Explicit conversion with CAST is preferable to relying on implicit type conversion because implicit casts can introduce both correctness and performance problems. The presentation shows that CAST(x AS type) is the ANSI form, while PostgreSQL also supports the x::type shorthand. Dirty text data should be validated before conversion, or TRY_CAST/TRY_CONVERT can be used where supported to turn failed conversions into NULL rather than aborting the query. A particularly important performance issue occurs when a cast is applied to an indexed column: a predicate such as amount::int = 100 may prevent a normal index from being used because the database must evaluate an expression on the column. Casting the parameter to the column’s native type is generally preferable. Similarly, text columns containing numeric identifiers should be compared with appropriately typed text literals rather than forcing the database to convert the entire column.
Financial calculations require particular attention to precision and the order of operations. The deck recommends keeping intermediate calculations exact and rounding at the business-defined reporting point rather than repeatedly rounding intermediate values. For example, line-level tax and totals can be calculated using exact numerics and rounded once according to the invoice’s reporting rules. Importantly, the sum of individually rounded lines can differ slightly from the rounded sum of exact values, so reconciliation logic may be necessary. Integer minor units provide another robust approach for monetary storage because they eliminate decimal representation issues while retaining exact arithmetic. Weighted averages should use the sum of products divided by the sum of weights rather than a simple average when observations carry different quantities, while functions such as PERCENTILE_CONT can provide median and percentile measures.
The broader lesson is that numeric correctness and query performance are closely connected to data types and conversion choices. Integers are efficient for keys and counts, NUMERIC provides exact decimal arithmetic for values requiring precision, and approximate floating-point types should be reserved for domains where approximation is acceptable. Common mistakes include storing money as FLOAT, accidentally performing integer division, allowing division by zero, rounding too early, casting indexed columns inside predicates, and assuming a particular rounding mode without testing the database and data type involved. Keeping predicates cast-free, choosing the narrowest exact type that fits the domain, using NULLIF for safe ratios, and documenting the business rounding rule produces SQL that is both more reliable and easier for the database optimizer to execute efficiently.