SQL ROUND: Round a Value and Keep Formatting Separate

The practice task

Round fictional sensor readings to two decimal places. Include positive, negative and already-short values, while avoiding an assumption about an exact decimal midpoint.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE readings(id INTEGER, value REAL);
INSERT INTO readings VALUES (1,12.344),(2,12.346),(3,-12.346),(4,12.3);

SELECT id, ROUND(value,2) AS rounded FROM readings ORDER BY id;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The first two readings fall on opposite sides of the two-place rounding boundary. The negative reading rounds to -12.35. The last value remains numerically 12.3; the result is not a promise to display the text 12.30.

This SQLite fixture uses floating-point values. Binary floating-point cannot represent every decimal fraction exactly, so a decimal-looking midpoint may not be the exact mathematical midpoint a reader imagines. Test the engine and type when a specific rounding policy matters.

Choose where rounding belongs in a calculation. Rounding every input before summing can differ from summing the original values and rounding the final total. State the intended measurement rule and preserve more precision until the step where the rule requires it.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Separate numeric rounding from text display formatting.
• Do not infer a universal midpoint policy across engines and numeric types.
• Avoid premature rounding of intermediate calculations.