SQL Numeric and Math Functions: ROUND, ABS and Division

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

ABS returns a number’s magnitude without its sign. ROUND changes a value to a requested precision according to the engine’s numeric rules. FLOOR selects an integer boundary at or below a number, while CEILING selects one at or above it. For negative inputs, truncation towards zero is not the same as FLOOR: floor of minus 1.2 is minus two. Do not interchange these operations simply because they produce the same result for a positive example.

Numeric storage affects the calculation before a display format is applied. DECIMAL or NUMERIC types can represent decimal values exactly in server databases, while floating-point types store approximations. Rounding a floating-point value at a halfway boundary may therefore behave differently from rounding an exact decimal. SQLite’s dynamic type system does not provide fixed-precision decimal arithmetic merely because a column is declared DECIMAL. Choose integer minor units or an approved decimal solution for exact money calculations there.

Division requires attention to both operand types and zero denominators. Integer division can truncate fractions in PostgreSQL, SQL Server and SQLite; MySQL’s slash operator has different numeric rules and offers DIV for integer division. Use a decimal operand or explicit cast when a fraction is needed. Wrap the denominator in NULLIF(denominator,0) if a zero denominator should yield an unknown ratio instead of an error or engine-specific result.

Most ordinary numeric functions propagate NULL, preserving an unknown input as unknown output. A report may intentionally apply COALESCE to a missing amount, but that is a business assumption rather than a mathematical identity. Round at the stage required by policy: rounding each line item and then summing can differ from summing exact values and rounding once. POWER, logarithms and trigonometric functions are available in many engines, but SQLite mathematical functions depend on how the library was built. Verify availability before writing portable exercises.

ROUND and ABS are available in all four systems. ROUND parameter options and halfway behaviour differ by type and engine. SQLite FLOOR, CEIL and POWER require the mathematical-function build option in ordinary SQLite; do not promise their availability without checking. SQL Server calls the upward function CEILING; names and aliases vary elsewhere.

Check the engine-specific syntax and version requirements in PostgreSQL mathematical functions, SQLite mathematical functions.

Return the numeric ratio of paid known revenue to all known revenue, rounded to three decimal places. Avoid a zero denominator.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT ROUND(
 SUM(CASE WHEN status='paid' THEN amount ELSE 0 END)*1.0
 /NULLIF(SUM(amount),0),3) AS paid_share FROM sales;

paid_share
0.647

The numerator is fifty-five and the denominator is eighty-five. Multiplying by 1.0 requests fractional numeric arithmetic, and NULLIF guards a zero total. The approximate ratio 0.6470588 is rounded to 0.647; it is a fraction, not a percentage label.

Compare integer-looking division with a decimal denominator in the chosen engine. Then evaluate a denominator guarded by NULLIF(0,0) and observe a NULL ratio. Test ROUND on a negative and a fractional value using the exact numeric type your application will use. Do not generalise floating-point halfway results to fixed-decimal calculations, or assume optional SQLite mathematical functions are installed.