Numeric Functions: ROUND, ABS & More

Strike rate = runs × 100 ÷ balls. For Virat Kohli that is 144.23076923… — nobody wants 14 decimals on a scoreboard. Numeric functions shape numbers for reports.

Definition

A numeric function is a single-row function that takes a number as input and returns a number — rounded, made positive, raised to a power, and so on.

Syntax

ROUND(number, decimal_places)
ABS(number)
CEILING(number)
FLOOR(number)

Example — ROUND and ABS on literal values

SELECT
    ROUND(3.516, 2) AS r2,
    ROUND(3.516, 1) AS r1,
    ROUND(3.516, 0) AS r0,
    ABS(-2.5)       AS abs_neg,
    ABS(2.5)        AS abs_pos,
    CEILING(3.2)    AS ceil,
    FLOOR(3.8)      AS flr,
    POWER(2, 3)     AS pwr,
    SQRT(16)        AS root;

Note: SQL Server keeps the same number of decimal places as the input, so it displays ROUND(3.516, 2) as 3.520, ROUND(3.516, 1) as 3.500 and ROUND(3.516, 0) as 4.000. The value is the same — only the display differs from the playground.

ABS is handy for negative values like a net run rate of −0.45 or "how far is each player from 500 runs", where only the size of the gap matters.

Example — strike rate with ROUND

Our player_profiles table has both runs and balls (the PDF's chapter-7 players table).

SELECT
    name,
    runs,
    balls,
    ROUND(runs * 100.0 / balls, 2) AS strike_rate
FROM player_profiles;

How it works: step 1 — divide runs by balls and multiply by 100 (strike rate = runs per 100 balls); step 2 — ROUND to 2 decimals. In SQL Server the column shows trailing zeros (e.g. 144.230000); wrap it in CAST(... AS DECIMAL(6,2)) for a clean 144.23.

The integer-division trap — why 100.0, not 100

When both sides of / are integers, SQL Server does integer division and throws away the decimals: 7 / 2 = 3, but 7 / 2.0 = 3.5.

SELECT
    name,
    runs * 100 / balls   AS int_sr,
    runs * 100.0 / balls AS decimal_sr
FROM player_profiles;

Writing 100.0 makes the expression decimal, so the fractional part survives until ROUND decides what to keep.

Example — ABS for "distance from 500 runs"

SELECT
    name,
    runs,
    runs - 500      AS diff,
    ABS(runs - 500) AS gap
FROM players;

Common mistakes

• ❌ runs * 100 / balls → integer result (144). ✅ runs * 100.0 / balls → 144.23…
• ❌ Expecting CAST(3.516 AS INT) to round. ✅ It truncates → 3. Use ROUND to round.
• ❌ Using ROUND to always round up. ✅ ROUND goes to the nearest value; use CEILING to always round up and FLOOR to always round down.
• ❌ Dividing by a column that can be 0 → "Divide by zero error" in SQL Server. ✅ NULLIF(balls, 0) (see NULL functions).

SQL Server vs MySQL / PostgreSQL

Tip: ROUND also accepts a negative length in SQL Server: ROUND(750, -2) = 800 (nearest hundred). The browser playground ignores negative lengths, so to try "nearest hundred" there use ROUND(runs / 100.0, 0) * 100.

Interview tip

Q: Why does SELECT 750 / 520 return 1 in SQL Server? Both operands are integers, so SQL Server performs integer division and discards the remainder. Make one operand decimal (750 * 1.0 / 520 or CAST(750 AS DECIMAL(10,2)) / 520) to get 1.44.