Real-World Numeric Calculations in SQL
Introduction
Real-world numeric calculations in SQL transform stored numbers into useful business information. A table may store a price, quantity, tax rate, salary, target, distance, or account balance, but users usually need a result derived from those values. Order totals, discounts, profit margins, commissions, inventory values, growth rates, interest, and quality percentages are all calculated data.
SQL is well suited to this work because calculation can happen close to the data and can be combined with filtering, grouping, joins, and aggregation. The challenge is not usually the multiplication or division itself. Reliable calculations require a precise business formula, suitable data types, known units, deliberate NULL behavior, safe denominators, and documented rounding points. A syntactically valid query can still produce a financially or operationally wrong result when one of those assumptions is unclear.
From Business Formula to SQL Expression
Begin with the formula in business language before writing SQL. If an order line total is price multiplied by quantity, write that rule explicitly. If tax applies after a discount rather than before it, capture that sequence. Translating a validated formula into SQL is much safer than constructing an expression while guessing about policy.
Business formula:
line_total = unit_price * quantity
SQL expression:
SELECT unit_price * quantity AS line_total
FROM order_item;
Meaningful aliases make derived columns part of a readable result contract. An unnamed expression such as price * quantity forces every consumer to rediscover its meaning. An alias such as line_total, gross_profit, or achievement_percentage preserves business intent.
Core Arithmetic Operators
SQL uses addition, subtraction, multiplication, and division for most business formulas. The remainder operation is available through a percent operator in some databases and through functions such as MOD() in others. Parentheses should make precedence explicit whenever a calculation contains multiple stages.
| Operator | Typical purpose | Example |
|---|---|---|
+ | Add amounts | subtotal + tax |
- | Find a difference | revenue - cost |
* | Scale a value | price * quantity |
/ | Calculate a ratio | distance / hours |
MOD() | Find a remainder | MOD(quantity, pack_size) |
Operator precedence normally evaluates multiplication and division before addition and subtraction. Even when the engine would interpret an expression correctly, parentheses communicate the intended order to reviewers and protect the formula when it is later extended.
Product and Order Line Totals
An order line is one of the simplest useful calculations. Each row combines quantity with the price that applies to that line. The calculation remains row-level until an aggregate combines several lines into an order subtotal.
SELECT order_id,
product_id,
quantity,
unit_price,
quantity * unit_price AS line_total
FROM order_item;
Production systems should clarify whether the price already includes tax, whether quantity can be fractional, and whether the line uses the current product price or a historical price captured when the order was placed. Recalculating old orders from a mutable catalog price can change history.
Order Subtotals with SUM
To calculate one subtotal per order, aggregate the row-level expression. SQL evaluates price * quantity for each qualifying row and then sums those values within each order_id group.
SELECT order_id,
SUM(quantity * unit_price) AS subtotal
FROM order_item
GROUP BY order_id;
The query should define which lines qualify. Cancelled, returned, free, or informational lines may follow different rules. A correct aggregate is based not only on the formula but also on the correct population of rows.
Discount Amount and Discounted Price
A percentage can be stored as a whole percent such as 20 or as a decimal ratio such as 0.20. Those designs require different formulas. Mixing them is a common source of one-hundred-fold errors.
-- discount_percent stores 20 for twenty percent
SELECT price,
price * discount_percent / 100.0 AS discount_amount,
price * (1 - discount_percent / 100.0) AS final_price
FROM product_offer;
-- discount_rate stores 0.20 for twenty percent
SELECT price,
price * discount_rate AS discount_amount,
price * (1 - discount_rate) AS final_price
FROM product_offer;
Name the column to reveal its unit and constrain valid values where possible. A discount_rate between zero and one is easier to interpret than an ambiguous column named simply discount.
Tax and Calculation Order
Tax calculations demonstrate why business sequence matters. If tax applies to the discounted amount, calculating it from the original subtotal produces the wrong result even though each individual arithmetic operation is valid.
SELECT subtotal,
subtotal * discount_rate AS discount_amount,
subtotal * (1 - discount_rate) AS taxable_amount,
subtotal * (1 - discount_rate) * tax_rate AS tax_amount,
subtotal * (1 - discount_rate) * (1 + tax_rate) AS final_total
FROM order_summary;
Real invoices can include taxable and non-taxable lines, multiple jurisdictions, shipping, coupons, exemptions, and tax-inclusive pricing. Store or join the authoritative rates and rules instead of hiding them as unexplained constants in many queries.
Governing Rates and Effective Dates
Tax, commission, discount, exchange, and interest rates change over time. A calculation should join the rate that was valid for the transaction date, not simply the newest row in a configuration table. Effective-dated data usually includes a start date and either an end date or a convention that the next start date closes the previous period.
SELECT o.order_id,
o.subtotal * r.tax_rate AS tax_amount
FROM customer_order o
JOIN tax_rate r
ON r.region_id = o.region_id
AND o.order_date >= r.effective_from
AND (r.effective_to IS NULL
OR o.order_date < r.effective_to);
Rate ranges should not overlap for the same jurisdiction and tax category. A uniqueness or exclusion strategy, validation job, and controlled approval process can prevent two rates from matching one transaction. For financial history, many systems also store the selected rate on the transaction so that the original result remains reproducible even if reference data is corrected later.
Profit, Loss, Margin, and Markup
Profit is revenue minus cost. A negative result represents a loss. Two related percentages, profit margin and markup, use different denominators and therefore answer different questions.
SELECT revenue,
cost,
revenue - cost AS profit_or_loss,
(revenue - cost) * 100.0 / NULLIF(revenue, 0)
AS profit_margin_percent,
(revenue - cost) * 100.0 / NULLIF(cost, 0)
AS markup_percent
FROM sales_summary;
For revenue of 100 and cost of 80, profit is 20, margin is 20 percent, and markup is 25 percent. Reporting one as the other can lead to incorrect pricing decisions. The alias and report label should identify which denominator is used.
General Percentage Calculations
The common percentage formula is part divided by total, multiplied by 100. Multiplying by 100.0 also encourages decimal arithmetic rather than integer division in databases where integer operands produce an integer result.
SELECT student_id,
obtained_marks * 100.0 / NULLIF(total_marks, 0)
AS percentage
FROM examination_result;
A ratio and a displayed percent are different representations. The ratio 0.90 corresponds to 90 percent. Decide which representation the database stores and which the interface returns. Do not multiply an already-percent value by 100 again.
Preventing Division by Zero
Any denominator can be zero unless the schema or business process proves otherwise. NULLIF(denominator, 0) is a common defensive pattern. It turns zero into NULL, so ordinary SQL arithmetic generally returns NULL instead of attempting to divide by zero.
SELECT employee_id,
actual_sales * 100.0 / NULLIF(target_sales, 0)
AS achievement_percentage
FROM sales_target;
Preventing an exception is only the technical part of the decision. A zero target may mean not assigned, not applicable, invalid configuration, or automatically achieved. The result should reflect that business meaning rather than silently replacing every undefined ratio with zero.
NULL Values in Arithmetic
Ordinary arithmetic involving NULL generally returns NULL because an unknown input makes the result unknown. That behavior is appropriate when a missing quantity or price means the total cannot be determined. Replacing NULL with zero changes the meaning and must be justified.
SELECT product_id,
COALESCE(quantity, 0) * unit_price AS calculated_amount
FROM inventory_adjustment;
This expression is correct only if a missing quantity is defined as zero. If NULL means that counting has not occurred, converting it to zero creates a false inventory value. Apply COALESCE() from a documented rule, not as a reflex.
Salary, Pay, and Commission
Payroll calculations combine rates, hours, thresholds, and eligibility rules. A basic gross-pay expression multiplies regular hours by the hourly rate and adds overtime hours at a defined multiplier.
SELECT employee_id,
regular_hours * hourly_rate AS regular_pay,
overtime_hours * hourly_rate * 1.5 AS overtime_pay,
regular_hours * hourly_rate
+ overtime_hours * hourly_rate * 1.5 AS gross_pay
FROM timesheet;
Real payroll must address overtime eligibility, pay periods, breaks, shift differentials, holidays, caps, taxes, and jurisdiction-specific rules. Such logic should be versioned and tested like application code. A literal multiplier is acceptable in a teaching example but usually belongs in controlled configuration or a rule table in production.
Salary Increases and Bonuses
A salary increase scales the current salary by a rate, while a bonus may depend on performance. A CASE expression can select the correct rate before the amount is calculated.
SELECT employee_id,
salary,
salary * increase_rate AS increase_amount,
salary * (1 + increase_rate) AS revised_salary,
CASE
WHEN performance_score >= 90 THEN salary * 0.10
WHEN performance_score >= 75 THEN salary * 0.05
ELSE 0
END AS performance_bonus
FROM employee_compensation;
Boundary values deserve explicit attention. A score of exactly 90 should enter the intended branch, and overlapping or missing ranges should be impossible. For changing policies, effective-dated rate tables are more maintainable than repeated CASE expressions.
Tiered Pricing
Utilities, shipping, storage, and subscriptions often charge different rates across usage bands. A two-tier electricity rule may charge one rate for the first 100 units and another rate for units beyond 100.
SELECT account_id,
units_consumed,
CASE
WHEN units_consumed <= 100
THEN units_consumed * 0.10
ELSE 100 * 0.10
+ (units_consumed - 100) * 0.15
END AS usage_charge
FROM electricity_usage;
The expression must state whether tiers are progressive or whether one rate applies to the entire quantity after a threshold. These policies produce different bills. Larger tier structures are usually easier to maintain in a table with lower bounds, upper bounds, rates, and effective dates.
Inventory Value and Cost of Goods Sold
Inventory value is commonly quantity on hand multiplied by unit cost. Cost of goods sold uses the quantity sold and the cost basis that applies to those units.
SELECT warehouse_id,
SUM(quantity_on_hand * unit_cost) AS inventory_value
FROM inventory_balance
GROUP BY warehouse_id;
This simple formula does not choose an accounting method. FIFO, LIFO, weighted average, standard cost, write-downs, and landed costs can produce different values. The query must operate on data prepared according to the organization's valuation policy.
Revenue and Gross Profit
Revenue is often quantity sold multiplied by selling price, while gross profit subtracts the corresponding unit cost. Combining the calculations in one query makes the relationship visible.
SELECT product_id,
SUM(quantity_sold * selling_price) AS revenue,
SUM(quantity_sold * unit_cost) AS cost_of_goods_sold,
SUM(quantity_sold * (selling_price - unit_cost))
AS gross_profit
FROM sale_line
GROUP BY product_id;
Refunds, returns, credits, and discounts should enter the calculation consistently. A report that counts returned quantity but ignores the related revenue reversal will not reconcile with accounting totals.
Simple and Weighted Averages
AVG(price) gives every qualifying row equal weight. That is not always the business question. When prices correspond to different quantities, a quantity-weighted average better represents the actual units sold.
SELECT SUM(unit_price * quantity) * 1.0
/ NULLIF(SUM(quantity), 0)
AS weighted_average_price
FROM sale_line;
Suppose 100 units sell for 10 and one unit sells for 100. The simple average of the two prices is 55, but the quantity-weighted average is approximately 10.89. Both calculations are mathematically valid; only one may answer the intended question.
Growth and Change
Absolute growth is current value minus previous value. Growth percentage divides that difference by the previous value. The base period is therefore essential to the interpretation.
SELECT period,
current_sales - previous_sales AS growth_amount,
(current_sales - previous_sales) * 100.0
/ NULLIF(previous_sales, 0) AS growth_percentage
FROM sales_comparison;
When the previous value is zero, a conventional percentage growth rate is undefined. Returning NULL and labeling the case clearly is usually more honest than inventing zero or an arbitrary maximum. Negative bases also need domain-specific interpretation.
Period-over-Period Calculations with LAG
When current and previous values are stored as separate period rows, a window function can obtain the prior result without a self-join. LAG() reads a value from an earlier row in the defined business sequence.
WITH monthly_sales AS (
SELECT month_start,
SUM(sale_amount) AS revenue
FROM sale
GROUP BY month_start
), compared AS (
SELECT month_start,
revenue,
LAG(revenue) OVER (ORDER BY month_start)
AS previous_revenue
FROM monthly_sales
)
SELECT month_start,
revenue,
revenue - previous_revenue AS change_amount,
(revenue - previous_revenue) * 100.0
/ NULLIF(previous_revenue, 0) AS change_percent
FROM compared;
The ordering column must represent a complete, unambiguous sequence. Missing months can make the previous row different from the previous calendar month, so reports may need a calendar table that supplies absent periods with an explicitly defined value. Partition the window by product, region, or account when each group needs its own comparison.
Budget Variance and Target Achievement
Variance compares actual performance with a plan. Some organizations define favorable variance as actual minus budget, while others reverse the sign for expenses. Document the convention so that positive and negative values have stable meaning.
SELECT department_id,
actual_amount - budget_amount AS variance_amount,
(actual_amount - budget_amount) * 100.0
/ NULLIF(budget_amount, 0) AS variance_percentage
FROM department_budget;
Target achievement uses actual divided by target, while remaining target uses target minus actual. Achievement may exceed 100 percent, and remaining target may become negative. Do not clamp these values unless the report requirement explicitly asks for a capped display.
Operational and Quality Metrics
The same ratio pattern supports conversion rate, click-through rate, defect rate, test pass percentage, and project completion. The SQL is short, but the numerator and denominator definitions control the metric.
SELECT execution_id,
passed_tests * 100.0 / NULLIF(total_tests, 0)
AS pass_percentage,
failed_tests * 100.0 / NULLIF(total_tests, 0)
AS failure_percentage
FROM test_execution;
Pass and failure percentages may not total 100 when tests are blocked, skipped, not run, or in progress. A reliable dashboard defines every state and either exposes all categories or explains the denominator used by each metric.
Simple Interest
Simple interest applies a rate to the original principal for a number of periods. When the stored rate is a whole percent, divide by 100; when it is a decimal ratio, use it directly.
SELECT investment_id,
principal * interest_percent * years / 100.0
AS simple_interest,
principal
+ principal * interest_percent * years / 100.0
AS maturity_amount
FROM investment;
The formula assumes that rate and time use compatible units. A yearly rate multiplied by months without conversion is wrong. Day-count conventions, fees, payment timing, and compounding rules must be defined for real financial products.
Compound Growth and Depreciation
POWER() supports exponential formulas. Compound growth multiplies principal by one plus the rate raised to the number of periods. Declining-balance depreciation uses one minus the depreciation rate.
SELECT principal * POWER(1 + annual_rate, years)
AS future_value
FROM investment;
SELECT original_value * POWER(1 - depreciation_rate, years)
AS estimated_value
FROM asset;
These formulas assume one compounding or depreciation step per stated period. Monthly compounding requires a periodic rate and a corresponding number of periods. Validate rate conversion and rounding with domain experts before using SQL results in contracts or financial statements.
Loan Payment Calculations
A fixed-payment loan formula can be expressed with arithmetic and POWER(), but the meaning of every input must be precise. Principal, periodic interest rate, payment count, payment timing, fees, and rounding conventions all affect the schedule.
SELECT principal *
(periodic_rate * POWER(1 + periodic_rate, payment_count)) /
NULLIF(POWER(1 + periodic_rate, payment_count) - 1, 0)
AS scheduled_payment
FROM loan_terms;
A zero-interest loan needs a separate branch because the standard denominator becomes zero. A CASE expression can return principal divided by payment count for that situation. Production loan systems should also create and reconcile an amortization schedule rather than relying only on a single payment estimate.
Unit Prices, Speed, and Consumption
Unit calculations divide one measure by another. Price per unit, average speed, fuel efficiency, energy cost, and cost per transaction are useful only when units are compatible and explicit.
SELECT product_id,
package_price / NULLIF(units_per_package, 0)
AS price_per_unit
FROM product_package;
SELECT trip_id,
distance_miles / NULLIF(hours_travelled, 0)
AS average_miles_per_hour
FROM trip;
The database does not know that one distance is miles and another is kilometers merely because both are numeric. Store unit metadata or normalize values to a canonical unit before comparing or combining them.
Fuel and Shipping Cost
Fuel cost combines distance, efficiency, and unit price. Shipping often combines a base charge with weight, distance, zone, or service-level charges.
SELECT trip_id,
distance_miles / NULLIF(miles_per_gallon, 0)
* price_per_gallon AS estimated_fuel_cost
FROM trip_estimate;
SELECT shipment_id,
base_cost + package_weight_kg * rate_per_kg
AS shipping_cost
FROM shipment_quote;
Input units should be visible in column names or governed by metadata. Multiplying pounds by a kilogram rate produces a plausible-looking but incorrect result. Conversion should be explicit and tested.
Distance and BMI Examples
Mathematical functions can combine with arithmetic for domain formulas. Cartesian distance uses POWER() and SQRT(). Body mass index divides weight in kilograms by height in meters squared.
SELECT SQRT(
POWER(x2 - x1, 2)
+ POWER(y2 - y1, 2)
) AS straight_line_distance
FROM coordinate_pair;
SELECT weight_kg / NULLIF(POWER(height_m, 2), 0) AS bmi
FROM measurement;
These examples show that a formula can be technically correct but still depend on valid units and input ranges. Geographic distance on the Earth requires a geospatial model rather than a simple Cartesian formula, and health metrics require appropriate context before interpretation.
CEIL, FLOOR, and Whole-Unit Rules
Some business results must be whole units. CEIL() is useful when any partial unit requires another complete unit, such as packages, vehicles, or billing blocks. FLOOR() is useful when only complete groups count.
SELECT CEIL(item_count * 1.0 / NULLIF(box_capacity, 0))
AS boxes_required
FROM packing_plan;
SELECT FLOOR(available_minutes / session_minutes)
AS complete_sessions
FROM schedule_capacity;
Do not substitute ordinary rounding for a directional rule. A requirement for enough boxes must round upward even when the fraction is small; a requirement for completed sessions must round downward.
Precision and Numeric Data Types
Choose data types from the domain. DECIMAL or NUMERIC is commonly appropriate for exact monetary and fixed-point values. Floating-point types are useful for approximate scientific measurements but can represent decimal fractions only approximately.
Precision and scale also affect intermediate expressions. Multiplication can require more integer and fractional digits than either operand, and a cast to a narrow type can overflow or round. Test calculations near maximum supported values instead of testing only ordinary amounts.
Avoiding Integer Division
In some databases, dividing two integer values performs integer division. The expression 1 / 2 can therefore produce zero instead of 0.5. Introduce a decimal operand or cast deliberately when a fractional result is required.
SELECT passed_count * 100.0
/ NULLIF(total_count, 0) AS pass_percentage
FROM test_summary;
Do not rely on an accidental implicit conversion caused by another column or literal. Explicit decimal arithmetic makes the intent portable and easier to review.
Rounding at the Correct Stage
ROUND() changes a numeric result according to the database's rounding behavior. It should be applied at the business-defined point rather than after every intermediate operation.
SELECT order_id,
ROUND(SUM(quantity * unit_price), 2) AS rounded_total
FROM order_item
GROUP BY order_id;
Rounding each line and then summing can differ from summing exact line values and rounding the final total. Invoices, tax rules, and financial standards may prescribe one method. The choice must be shared by SQL, application code, reports, and reconciliation processes.
Calculation Versus Formatting
A calculation should normally return a numeric value. Currency symbols, grouping separators, percentage signs, and localized decimal markers are presentation concerns. Formatting too early converts numbers to text and can break arithmetic, numeric sorting, filtering, and aggregation.
Keep final_total numeric through the calculation pipeline. Format it in the application or at the final reporting boundary. When both machine and human consumers need the result, expose a raw numeric field alongside an explicitly named display field.
Reusable Calculations with CTEs
Complex formulas become easier to understand when each business stage receives a name. A common table expression can calculate a subtotal once, then derive discount, tax, and final total from that result.
WITH order_base AS (
SELECT order_id,
SUM(quantity * unit_price) AS subtotal
FROM order_item
GROUP BY order_id
), priced_order AS (
SELECT b.order_id,
b.subtotal,
b.subtotal * o.discount_rate AS discount_amount,
b.subtotal * (1 - o.discount_rate) AS taxable_amount,
o.tax_rate
FROM order_base b
JOIN customer_order o ON o.order_id = b.order_id
)
SELECT order_id,
subtotal,
discount_amount,
taxable_amount * tax_rate AS tax_amount,
taxable_amount * (1 + tax_rate) AS final_total
FROM priced_order;
This structure exposes each stage for testing and prevents accidental duplication of a long formula. A database view can provide similar reuse when the calculation is stable and shared across many queries.
Calculation Performance
Row-level arithmetic is usually inexpensive, but large scans, repeated expressions, joins, grouping, and scalar functions can make a report costly. Filter the correct rows early, maintain useful indexes for joins and predicates, and aggregate at the required grain.
Do not optimize by changing the meaning of the formula. Measure execution plans and realistic data volumes first. Persisted calculated values can improve read performance, but they introduce synchronization and audit responsibilities when source data or business rules change.
Historical Reproducibility
A report generated today should be able to explain a result produced last year. Reproducibility requires more than retaining the final number. Keep the source transaction values, selected rates, rule version, currency, units, rounding policy, and calculation timestamp needed to reconstruct the result.
Mutable reference data creates particular risk. If a product price, exchange rate, or commission schedule is overwritten, rerunning an old query may produce a new answer. Transaction snapshots, effective-dated dimensions, append-only ledgers, or calculation-version identifiers help preserve history. The right pattern depends on the domain, but the system should make changes visible rather than silently rewriting past meaning.
Audit Columns and Calculation Evidence
Important calculations should expose enough intermediate values to support investigation. An invoice report is easier to audit when it includes subtotal, discount rate, discount amount, taxable amount, tax rate, tax amount, and final total instead of returning only the final number.
Intermediate columns do not have to appear on the customer-facing document. They can be retained in an audit table, calculation event, or internal report. This evidence distinguishes a source-data problem from a formula error, rate-selection error, rounding difference, or formatting issue. For regulated or high-value calculations, record who or what initiated the calculation and which approved rule set was used.
Validation and Reconciliation
Validate calculations with independently known examples. A useful test fixture includes a normal value, zero, NULL, negative values, fractional values, maximum expected amounts, threshold boundaries, and rates at their permitted limits.
Reconciliation compares detailed results with trusted totals. The sum of invoice lines should match the invoice subtotal under the chosen rounding policy; inventory valuation should reconcile with the accounting ledger; payroll totals should reconcile with approved payroll output. Differences often expose missing rows or incorrect business scope rather than arithmetic mistakes.
Common Mistakes
Frequent errors include using the wrong percentage denominator, mixing whole percentages with decimal rates, allowing integer division, dividing by zero, replacing meaningful NULL values with zero, and confusing margin with markup. Other mistakes include mixing units, applying tax in the wrong sequence, hard-coding changing rates, and recalculating historical transactions from current reference data.
Rounding too early can accumulate differences, while formatting too early converts useful numbers into text. Complex expressions without parentheses or aliases are difficult to review. A technically successful query is not complete until its business assumptions are explicit.
Production Best Practices
- Write and approve the business formula before translating it into SQL.
- Use meaningful names that expose rates, units, and result meaning.
- Choose numeric types that support the required precision and range.
- Make decimal arithmetic explicit when division must preserve fractions.
- Protect denominators and define what a zero denominator means.
- Replace NULL only when the business rule defines a replacement value.
- Use parentheses to show calculation order.
- Keep units and currencies compatible before combining values.
- Store configurable rates in governed tables rather than unexplained literals.
- Apply rounding at documented business boundaries.
- Keep numeric calculation separate from display formatting.
- Test boundaries and reconcile derived totals with trusted sources.
Interview Questions and Answers
What are real-world numeric calculations in SQL? They are expressions that derive business or operational values such as totals, tax, profit, percentages, commission, inventory value, and growth from stored numeric data.
How do you calculate a percentage? Divide the part by the total and multiply by 100, using decimal arithmetic and protecting a possible zero denominator.
Why use NULLIF in division? NULLIF(denominator, 0) changes a zero denominator to NULL and commonly avoids a divide-by-zero error. The business meaning of the resulting NULL must still be defined.
What is the difference between margin and markup? Margin divides profit by revenue; markup divides profit by cost.
Why use DECIMAL for money? DECIMAL or NUMERIC supports exact fixed-point decimal values, while binary floating-point values are approximate.
When should SQL calculations be rounded? At the stage required by the business policy, such as per line, per tax component, or after aggregation.
Why use a weighted average? It reflects the influence of quantity or another weight instead of treating every row equally.
Should SQL return formatted currency strings? Usually calculations should remain numeric and formatting should happen at the presentation boundary, unless a fixed report contract explicitly requires formatted text.
Complete E-Commerce Example
Consider an item priced at 100, a quantity of three, a discount rate of 0.10, and a tax rate of 0.08. The subtotal is 300. The discount is 30, leaving 270 taxable. Tax is 21.60, and the final total is 291.60.
SELECT price * quantity AS subtotal,
price * quantity * discount_rate AS discount_amount,
price * quantity * (1 - discount_rate) AS taxable_amount,
price * quantity * (1 - discount_rate) * tax_rate
AS tax_amount,
price * quantity * (1 - discount_rate) * (1 + tax_rate)
AS final_total
FROM order_item;
The formula is compact because each rate is stored as a decimal ratio. A production implementation should still define line eligibility, discount allocation, tax jurisdiction, currency, rounding, refunds, and historical rate retention.
Key Takeaway
Real-world numeric SQL is the disciplined translation of business rules into data calculations. Arithmetic operators provide the mechanics, but reliability comes from correct types, units, populations, denominators, NULL semantics, rounding, and validation.
RELIABLE SQL CALCULATION = APPROVED BUSINESS FORMULA
+ CORRECT NUMERIC DATA
+ EXPLICIT UNITS AND RATES
+ SAFE NULL AND ZERO HANDLING
+ DOCUMENTED ROUNDING
+ BOUNDARY TESTS
+ RECONCILIATION
Start with the meaning, calculate with numeric values, validate against known outcomes, and format only at the final boundary. That process turns raw columns into useful results without losing accuracy, auditability, or business context.