Arithmetic & Numeric Functions: Examples

Calculate Order Totals with Tax

basic

E-commerce checkout calculates subtotal, tax, and total

SELECT

order_id,

price * quantity AS subtotal,

ROUND(price * quantity * 0.08, 2) AS tax,

ROUND(price * quantity * 1.08, 2) AS total

FROM order_items;

order_id | subtotal | tax | total

1001 | 100.00 | 8.00 | 108.00

1002 | 250.00 | 20.00 | 270.00

Arithmetic operators calculate values. ROUND formats to 2 decimal places for currency.

All

Finance Precision - DECIMAL vs FLOAT

basic

Payment processing requires exact decimal arithmetic to prevent $0.01 discrepancies

-- Wrong: FLOAT rounding error

SELECT 0.1 + 0.2 AS float_result;

-- Returns: 0.30000000000000004 (WRONG!)

-- Correct: DECIMAL exact precision

SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)) AS decimal_result;

-- Returns: 0.30 (CORRECT)

float_result: 0.30000000000000004

decimal_result: 0.30

Prevents payment discrepancies in financial systems.

FLOAT uses binary representation causing rounding errors. DECIMAL provides exact precision required for money. Always use DECIMAL for financial calculations.

All

Statistical Aggregates for Analytics

advanced

HR analytics dashboard calculates salary statistics for compensation analysis

SELECT

department,

COUNT(*) AS employee_count,

AVG(salary) AS mean_salary,

STDDEV(salary) AS std_dev,

MIN(salary) AS min_salary,

MAX(salary) AS max_salary

FROM employees

GROUP BY department;