Significant Figures in SQL: Rounding in Queries and Reporting

Rule Statement

In the context of SQL and relational databases, a critical distinction must be made between decimal precision (the number of digits after the decimal point) and significant figures (the total number of digits that carry meaningful information about the precision of a measurement). Standard SQL functions like ROUND(), FLOOR(), and CEILING() operate on decimal precision, not significant figures.

To maintain metrological integrity in reporting, the rule is as follows: The precision of a calculated result in a SQL query must not exceed the precision of the least precise input operand, governed by the rules of multiplication/division (fewest sig figs) or addition/subtraction (fewest decimal places). Failure to apply these rules leads to “false precision,” where a database reports a value like 12.3456789 when the underlying measurement was only accurate to two figures.

Standards Citation

Precision reporting in SQL should adhere to international metrology standards to ensure data interoperability and scientific validity:

• ISO 80000-1: Specifies the general rules for quantities and units, emphasizing that the number of digits expressed in a numerical value should reflect the uncertainty of the measurement.
• ASTM E29-17: Provides the standard practice for using significant digits in rounded numbers. It defines the “Round Half-Up” and “Round Half-to-Even” (Banker’s Rounding) methods, the latter of which is often the default in many programming environments but not necessarily in all SQL dialects.
• GUM (Guide to the Expression of Uncertainty in Measurement): Clause 4 outlines the propagation of uncertainty, which dictates how the number of significant figures in a SQL-calculated aggregate (like AVG() or SUM()) should be determined based on the input variance.

Software Behavior Note

Different SQL engines handle numeric types and rounding in ways that can silently compromise significant figures. Understanding these behaviors is essential for any engineer or data scientist.

Floating Point vs. Fixed Point

Using FLOAT or REAL types introduces binary floating-point inaccuracies (IEEE 754). For example, a value stored as 0.1 might actually be 0.10000000149. When applying ROUND() to these types, the result may be unpredictable. For high-precision reporting, DECIMAL(p, s) or NUMERIC types are mandatory, as they store values as exact fixed-point numbers.

The ROUND() Function Limitation

The standard SQL ROUND(value, precision) function targets the scale (digits to the right of the decimal). It does not target the precision (total significant digits). To round to three significant figures, a dynamic calculation is required because the position of the decimal point varies relative to the first non-zero digit.

Worked Examples

Since SQL lacks a native SIG_FIGS() function, we must implement the logic using logarithms to find the magnitude of the number.

Example 1: Rounding to 3 Significant Figures

Scenario: We have a column measurement with values like 123.456 and 0.00123456. We need to report both to 3 significant figures.

1. Step 1: Find the magnitude. Use LOG10(ABS(value)). For 123.456, the floor of the log is 2. For 0.00123456, it is -3.
2. Step 2: Calculate the required scale. The formula is (sig_figs - 1) - FLOOR(LOG10(ABS(value))).
3. Step 3: Apply ROUND().

SQL Logic:

SELECT ROUND(measurement, 3 - 1 - FLOOR(LOG10(ABS(measurement)))) FROM sensor_data;

Example 2: Handling Addition (Least Precise Decimal)

Scenario: Adding 10.1 (1 decimal place) and 2.005 (3 decimal places). The result must be rounded to 1 decimal place.

Query: SELECT ROUND(10.1 + 2.005, 1); → Result: 12.1

Counter-Examples

Avoid these common pitfalls to prevent reporting errors:

Common Mistakes

• Confusing Precision with Scale: Many developers use ROUND(val, 2) thinking it gives two significant figures. In reality, for the number 123.456, it gives 123.46 (5 sig figs), but for 0.0123456, it gives 0.01 (1 sig fig).
• Double Rounding: Rounding a value in a View and then rounding it again in a final Report query. This can shift the final digit incorrectly (e.g., 0.0445 → 0.045 → 0.05).
• Ignoring Zeroes: Failing to account for trailing zeros in string conversion. In metrology, 1.20 is different from 1.2; the former implies higher precision. SQL DECIMAL types often strip these unless formatted as strings.

Quick Reference Table

Use this table to determine the correct SQL approach based on the required metrological outcome.

This guide is part of our comprehensive precision and rounding reference library. While our site provides the most accurate significant figures calculator available, we encourage users to study these underlying rules to ensure their database reporting meets professional scientific standards. For more on rounding logic, see our articles on Rounding Methods and ASTM E29 Standards.