Numeric and Mathematical Functions

Difficulty: Intermediate
Generated on: 2025-07-10 02:26:31
Category: SQL Cheatsheet for Database Development

---

SQL Numeric and Mathematical Functions Cheatsheet (Intermediate)

1. Quick Overview

Numeric and Mathematical functions in SQL allow you to perform calculations, manipulate numbers, and analyze data. They’re crucial for reporting, data analysis, financial calculations, and scientific applications within your database. This cheatsheet focuses on commonly used functions and optimization techniques.

2. Syntax

-- Basic Syntax (Generic)
SELECT function_name(argument1, argument2, ...)
FROM table_name
WHERE condition;

3. Common Use Cases

• Financial Calculations: Calculating interest, discounts, profit margins.
• Statistical Analysis: Computing averages, standard deviations, variances.
• Data Transformation: Rounding, truncating, and converting numeric data.
• Engineering Applications: Performing complex mathematical operations.
• Reporting: Formatting numeric data for reports.

4. Best Practices

• Index Usage: Mathematical functions applied to columns can prevent index usage. Consider pre-calculating values or using computed columns/indexed views (where applicable) to improve performance.
• Data Types: Ensure you are using appropriate numeric data types (e.g., DECIMAL for financial data, FLOAT for scientific data) to avoid precision issues.
• Null Handling: Be aware of how functions handle NULL values. Use COALESCE or ISNULL to handle potential NULLs gracefully.
• Function Choice: Select the most appropriate function for the task. For example, use ROUND for general rounding, TRUNCATE (or FLOOR depending on the database) for truncation.
• Avoid Excessive Computation: Minimize complex calculations within the WHERE clause to allow the query optimizer to work effectively.
• Computed Columns (SQL Server/Oracle): If a calculation is performed repeatedly, consider using computed columns (SQL Server) or virtual columns (Oracle) to store the pre-calculated values. Index them for faster lookups.
• Materialized Views (PostgreSQL/Oracle): For complex calculations on large datasets, materialized views can significantly improve performance by storing the results. Refresh them periodically.

5. Examples

Sample Data (assuming a table named products)

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    price DECIMAL(10, 2),
    quantity INT,
    discount_rate DECIMAL(5, 2)
);

INSERT INTO products (product_id, product_name, price, quantity, discount_rate) VALUES
(1, 'Laptop', 1200.00, 10, 0.10),
(2, 'Mouse', 25.00, 50, 0.05),
(3, 'Keyboard', 75.00, 20, 0.00),
(4, 'Monitor', 300.00, 15, 0.15),
(5, 'Headphones', 100.00, 30, 0.08);

5.1. ROUND() - Rounding Numbers

-- Round the price to the nearest whole number
SELECT product_name, ROUND(price, 0) AS rounded_price
FROM products;

-- Output:
-- product_name | rounded_price
-- -------------|---------------
-- Laptop       | 1200
-- Mouse        | 25
-- Keyboard     | 75
-- Monitor      | 300
-- Headphones   | 100

5.2. FLOOR() - Rounding Down

-- Round the price down to the nearest whole number
SELECT product_name, FLOOR(price) AS floor_price
FROM products;

-- Output:
-- product_name | floor_price
-- -------------|--------------
-- Laptop       | 1200
-- Mouse        | 25
-- Keyboard     | 75
-- Monitor      | 300
-- Headphones   | 100

5.3. CEILING() - Rounding Up

-- Round the price up to the nearest whole number
SELECT product_name, CEILING(price) AS ceiling_price
FROM products;

-- Output:
-- product_name | ceiling_price
-- -------------|---------------
-- Laptop       | 1200
-- Mouse        | 25
-- Keyboard     | 75
-- Monitor      | 300
-- Headphones   | 100

5.4. TRUNCATE() (MySQL) or TRUNC() (Oracle) - Truncating Numbers

-- MySQL Example: Truncate the price to two decimal places
SELECT product_name, TRUNCATE(price, 0) AS truncated_price
FROM products;

-- Oracle Example: Truncate the price to two decimal places
SELECT product_name, TRUNC(price, 0) AS truncated_price
FROM products;


-- Output:
-- product_name | truncated_price
-- -------------|-----------------
-- Laptop       | 1200
-- Mouse        | 25
-- Keyboard     | 75
-- Monitor      | 300
-- Headphones   | 100

5.5. ABS() - Absolute Value

--  Calculate the absolute difference between the price and 100 (hypothetical target price)
SELECT product_name, ABS(price - 100) AS price_difference
FROM products;

-- Output:
-- product_name | price_difference
-- -------------|------------------
-- Laptop       | 1100
-- Mouse        | 75
-- Keyboard     | 25
-- Monitor      | 200
-- Headphones   | 0

5.6. MOD() - Modulo (Remainder)

-- Find products with a product_id that is an odd number
SELECT product_name
FROM products
WHERE MOD(product_id, 2) = 1;

-- Output:
-- product_name
-- -------------
-- Laptop
-- Keyboard
-- Headphones

5.7. POWER() - Exponentiation

-- Calculate the square of the quantity
SELECT product_name, POWER(quantity, 2) AS quantity_squared
FROM products;

-- Output:
-- product_name | quantity_squared
-- -------------|------------------
-- Laptop       | 100
-- Mouse        | 2500
-- Keyboard     | 400
-- Monitor      | 225
-- Headphones   | 900

5.8. SQRT() - Square Root

-- Calculate the square root of the quantity
SELECT product_name, SQRT(quantity) AS square_root_quantity
FROM products
WHERE quantity > 0;

-- Output:
-- product_name | square_root_quantity
-- -------------|----------------------
-- Laptop       | 3.1622776601683795
-- Mouse        | 7.0710678118654755
-- Keyboard     | 4.47213595499958
-- Monitor      | 3.872983346207417
-- Headphones   | 5.477225575051661

5.9. SIGN() - Sign of a Number

-- Determine if the price difference is positive, negative, or zero compared to 1000
SELECT product_name, SIGN(price - 1000) AS price_sign
FROM products;

-- Output:
-- product_name | price_sign
-- -------------|------------
-- Laptop       | 1
-- Mouse        | -1
-- Keyboard     | -1
-- Monitor      | -1
-- Headphones   | -1

5.10. LOG() and EXP() - Logarithm and Exponential

-- Calculate the natural logarithm of the quantity (greater than 0)
SELECT product_name, LOG(quantity) AS natural_log_quantity
FROM products
WHERE quantity > 0;

-- Calculate e raised to the power of 2
SELECT EXP(2) AS e_squared;

5.11. Calculating Discounted Price

-- Calculate the discounted price for each product
SELECT
    product_name,
    price,
    discount_rate,
    price * (1 - discount_rate) AS discounted_price
FROM products;

-- Output:
-- product_name | price | discount_rate | discounted_price
-- -------------|-------|---------------|------------------
-- Laptop       | 1200  | 0.10          | 1080.00
-- Mouse        | 25    | 0.05          | 23.75
-- Keyboard     | 75    | 0.00          | 75.00
-- Monitor      | 300   | 0.15          | 255.00
-- Headphones   | 100   | 0.08          | 92.00

5.12. Calculating Total Value

-- Calculate the total value of each product in stock
SELECT
    product_name,
    price,
    quantity,
    price * quantity AS total_value
FROM products;

-- Output:
-- product_name | price | quantity | total_value
-- -------------|-------|----------|-------------
-- Laptop       | 1200  | 10       | 12000.00
-- Mouse        | 25    | 50       | 1250.00
-- Keyboard     | 75    | 20       | 1500.00
-- Monitor      | 300   | 15       | 4500.00
-- Headphones   | 100   | 30       | 3000.00

6. Common Pitfalls

• Division by Zero: Always check for potential division by zero. Use CASE statements or NULLIF to prevent errors.

  -- Example: Prevent division by zero
  SELECT
      CASE
          WHEN quantity = 0 THEN NULL  -- Or some other appropriate value
          ELSE price / quantity
      END AS price_per_unit
  FROM products;
  
  -- Using NULLIF (more concise, but less flexible)
  SELECT price / NULLIF(quantity, 0) AS price_per_unit FROM products;

• Data Type Mismatches: Ensure that the data types of the arguments are compatible with the function. Implicit conversions can lead to unexpected results. Use CAST or CONVERT (SQL Server) for explicit conversions.

• Precision Issues: Floating-point numbers can have precision limitations. Use DECIMAL or NUMERIC for financial calculations where accuracy is critical.

• Incorrect Rounding: Understand the behavior of different rounding functions (e.g., ROUND rounds to the nearest number, while TRUNCATE simply removes digits).

• Ignoring NULLs: Be aware of how mathematical functions handle NULL values. Most functions will return NULL if any argument is NULL. Use COALESCE or ISNULL to handle NULLs appropriately.

  -- Example: Handling NULL discount rates
  SELECT
      product_name,
      price * (1 - COALESCE(discount_rate, 0)) AS discounted_price  -- If discount_rate is NULL, treat it as 0
  FROM products;

• Performance Bottlenecks: Applying functions to columns in the WHERE clause can prevent index usage. Consider computed columns or indexed views to pre-calculate values.

7. Database Variations

Feature	MySQL	PostgreSQL	SQL Server	Oracle
Truncation	TRUNCATE(x, d)	TRUNC(x) (truncates to integer part)	ROUND(x, d, 1) (1 means truncate)	TRUNC(x, d)
Null Handling	IFNULL(expr1, expr2)	COALESCE(expr1, expr2, ...)	ISNULL(expr1, expr2)	NVL(expr1, expr2)
Division by Zero	Returns NULL (by default)	Raises an error (can be handled)	Raises an error (can be handled)	Raises an error (can be handled)
Computed Columns	N/A	Supported (Virtual Columns)	Supported	Supported (Virtual Columns)
Materialized Views	N/A	Supported	Supported (Indexed Views, similar concept)	Supported
PI	PI()	PI()	PI()	PI (no parentheses)
Random Number	RAND()	RANDOM() (returns value between 0 and 1)	RAND()	DBMS_RANDOM.VALUE

Important Notes:

• TRUNCATE vs TRUNC: MySQL uses TRUNCATE(x, d) where d is the number of decimal places. Oracle uses TRUNC(x, d). PostgreSQL’s TRUNC(x) truncates to the integer part only. For PostgreSQL, consider TRUNC(x, d) using NUMERIC data type for the column. SQL Server uses ROUND(x, d, 1) for truncation.

• Null Handling: While COALESCE is ANSI SQL standard, ISNULL (SQL Server) and NVL (Oracle) can be more concise in some cases. IFNULL is MySQL-specific.

• Database-Specific Functions: Each database system offers a wider range of mathematical functions beyond the core set covered here. Consult the specific database documentation for a complete list.

This cheatsheet provides a solid foundation for working with numeric and mathematical functions in SQL. Remember to consult your database system’s documentation for the most accurate and up-to-date information. Always test your queries thoroughly before deploying them to production.