beginner

Numeric Functions

By DataPlayArena Technical Review BoardUpdated 2026-07-235 min readRuntime Verified

Overview

Numeric functions execute mathematical, rounding, and exponential calculations on integer or decimal columns.

Learning Objectives

  • Use ROUND(), CEIL(), and FLOOR() to adjust floating point values.
  • Calculate absolute differences with ABS() and remainders using MOD().
  • Perform advanced mathematical exponential calculations with POWER() and SQRT().

Detailed Concept Explanation

  • ROUND(numeric, decimals): Rounds a value to the specified number of decimal places.
  • CEIL(numeric) (or CEILING): Rounds upward to the nearest integer.
  • FLOOR(numeric): Rounds downward to the nearest integer.
  • ABS(numeric): Returns the absolute positive value of a number.
  • MOD(dividend, divisor) (or %): Returns the remainder of a division.
  • POWER(base, exponent): Raises a number to the power of another.

Code Examples

SQL

sql
-- Round average item rating and calculate floor/ceil limits
SELECT 
  price,
  ROUND(price, 1) AS rounded_price,
  CEIL(price) AS ceiling_price,
  FLOOR(price) AS floor_price
FROM products;

-- Identify odd-numbered transactions using modulo
SELECT transaction_id, amount
FROM transactions
WHERE MOD(transaction_id, 2) != 0;

Best Practices

  • Numeric Precision: When dividing integers, cast columns explicitly to decimal types (e.g., CAST(col AS DECIMAL)) to avoid automatic integer truncation (where 1 / 2 yields 0).

Interview Perspective

Warning

Common Pitfall: Integer Division

In SQL, 5 / 2 often evaluates to 2 instead of 2.5 because the operands are integers. To get the decimal value, multiply by 1.0 or cast: 5 * 1.0 / 2.


Interactive Challenges

Challenge 1: Round Product Price

Select 'name' and the 'price' rounded to 2 decimal places (alias 'final_price') from the 'products' table.


Summary

Numeric functions handle mathematical rounding, absolute values, and division operations to control numerical precision.

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.