Column Calculations — Arithmetic & Expressions

SQL supports five arithmetic operators: addition (+), subtraction (-), multiplication (*), division (/), and modulo (%). Modulo returns the remainder after integer division — 10 % 3 returns 1 because 10 divided by 3 is 3 with a remainder of 1.

The precedence order mirrors standard mathematics: multiplication, division, and modulo are evaluated first (left to right when they appear together), then addition and subtraction (left to right). Parentheses override all precedence rules — expressions inside parentheses are always evaluated first, regardless of what operators are inside or outside them.

A practical precedence example: 10 + 5 * 2 evaluates as 10 + 10 = 20, not 15 * 2 = 30. To get 30, write (10 + 5) * 2. For margin percentage: unit_price - cost_price / unit_price * 100 is evaluated as unit_price - (cost_price / unit_price * 100), which is wrong. The correct formula requires parentheses: (unit_price - cost_price) / unit_price * 100. Precedence bugs in financial calculations are particularly dangerous because the query runs without error but produces incorrect numbers that may be used in business decisions.

Integer division occurs when both operands in a division expression are integers — the database returns an integer result, discarding the decimal portion. The result is truncated (not rounded): 7 / 2 returns 3 in PostgreSQL and MySQL, not 3.5. This is mathematically correct in integer arithmetic but almost never what SQL analysts intend when calculating ratios or percentages.

The danger is that no error occurs — the query runs successfully and returns results that appear plausible but are wrong. If a products table has INTEGER columns for unit_price and cost_price, the margin calculation (unit_price - cost_price) / unit_price would return 0 for every product where profit is less than the selling price — which is virtually every product. The analyst would see 0% margin across the board and have no indication that the division was truncated.

Three reliable solutions: multiply by 1.0 before dividing — (unit_price - cost_price) * 1.0 / unit_price — which promotes the result to decimal. Use CAST to explicitly convert to a decimal type — CAST(unit_price - cost_price AS DECIMAL) / unit_price. Or use a decimal literal in the numerator or denominator — (unit_price - cost_price) / 1.0. The CAST approach is most explicit about intent and is recommended in production code where the data types of columns may not be immediately obvious to someone reading the query later.

All three convert a decimal number to a controlled precision, but they round in different directions. ROUND(number, places) rounds to the specified number of decimal places using standard rounding — values ending in .5 or higher round up, below .5 round down. ROUND(3.45, 1) returns 3.5. ROUND(3.44, 1) returns 3.4. ROUND with a negative places argument rounds to the left of the decimal: ROUND(1234, -2) returns 1200.

CEIL (or CEILING) always rounds up to the nearest integer — away from zero for positive numbers. CEIL(4.1) returns 5. CEIL(4.9) returns 5. CEIL(4.0) returns 4. CEIL is used for "how many containers do I need?" problems — if you have 22 items and containers hold 10, you need CEIL(22/10) = 3 containers, not 2.2.

FLOOR always rounds down to the nearest integer — towards zero for positive numbers. FLOOR(4.9) returns 4. FLOOR(4.1) returns 4. FLOOR(4.0) returns 4. FLOOR is used for bucketing and banding — FLOOR(salary / 10000) * 10000 gives the bottom of each ₹10,000 salary band. The trio covers three essential rounding patterns: nearest value (ROUND), always up (CEIL), always down (FLOOR).

When arithmetic is applied to a column in a WHERE condition — WHERE unit_price * 1.18 >200 — the database must calculate unit_price * 1.18 for every single row in the table before it can determine whether the row qualifies. This prevents the database from using an index on unit_price, because the index stores raw unit_price values, not the result of unit_price * 1.18. The result is a full table scan even when the unit_price column has an index.

When the arithmetic is moved to the literal side — WHERE unit_price > 200 / 1.18 — the database evaluates 200 / 1.18 once (it is a constant, independent of any row), producing approximately 169.49. The WHERE condition becomes WHERE unit_price > 169.49, which the database can evaluate using an index on unit_price directly. Instead of a full table scan, the database does an index range scan — a dramatically more efficient operation on large tables.

This principle is called SARGability (Search ARGument Ability). A WHERE condition is SARGable if the database can use an index to satisfy it. Conditions that apply functions or arithmetic to columns are not SARGable. Conditions that compare raw column values to constants (even computed constants) are SARGable. The rule: whenever possible, isolate the column in the WHERE condition — put all transformations on the literal side. This applies to arithmetic, functions (LOWER, YEAR, MONTH), and any other operation that wraps a column value.

Date arithmetic in SQL calculates intervals between dates or adds/subtracts time from a date. The concept is universal — every SQL database supports it — but the syntax differs significantly across databases, which is one of the most common portability issues when moving SQL between systems.

In PostgreSQL and DuckDB (used in this playground), subtracting two DATE values returns an integer representing the number of days: delivery_date - order_date returns 2 if delivery was 2 days after the order. Adding an integer to a date adds that many days: order_date + 7 returns the date 7 days later. PostgreSQL also supports INTERVAL arithmetic: order_date + INTERVAL '1 month' adds exactly one month. In MySQL, date subtraction is done with the DATEDIFF function: DATEDIFF(delivery_date, order_date). Date addition uses DATE_ADD: DATE_ADD(order_date, INTERVAL 7 DAY). In SQL Server, DATEDIFF(day, order_date, delivery_date) for difference and DATEADD(day, 7, order_date) for addition.

The safest cross-database approach is to use explicit functions rather than operator overloading. DATEDIFF is supported in MySQL and SQL Server (with different argument order). PostgreSQL's date subtraction operator is elegant but PostgreSQL-specific. When writing SQL that must run on multiple database types, document which database it targets or use a database abstraction layer that normalises these differences. In practice, most organisations standardise on one database per workload, making dialect differences a concern primarily when migrating systems or working with multiple clients on different stacks.

Cause: Integer division truncation. Both sides of the division are integers (or the result of integer subtraction is an integer), and dividing one integer by another returns an integer in PostgreSQL and MySQL. Since the profit (unit_price - cost_price) is always less than unit_price for any product sold at a markup, integer division always returns 0. The query runs without error and returns 0 for every product — a silent data quality failure.

Fix: Force decimal division by casting: CAST(unit_price - cost_price AS DECIMAL) / unit_price * 100. Or multiply by 1.0 first: (unit_price - cost_price) * 1.0 / unit_price * 100. Verify by checking the column data types: SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'products'. If unit_price is INTEGER, DECIMAL division must be forced explicitly.

Cause: One or more rows have a zero value in the denominator of a division expression. This is most common when dividing by a column that should theoretically be non-zero but has zero values due to data entry errors, default values, or products added with a unit_price of 0. In PostgreSQL, division by zero raises an error and aborts the entire query. In MySQL, division by zero returns NULL (not an error) — which can mask the issue.

Fix: Wrap the denominator in NULLIF to convert zero to NULL before dividing: (profit / NULLIF(unit_price, 0)) * 100. Dividing by NULL returns NULL instead of raising an error. The affected rows show NULL in the result, clearly indicating the calculation is not applicable. Also investigate why zero values exist in the denominator column — add a CHECK constraint to prevent unit_price = 0 if that is invalid data: CHECK (unit_price > 0).

Cause: Operator precedence bug. The calculation runs without error but evaluates in a different order than intended. For example, unit_price - cost_price / unit_price * 100 is evaluated as unit_price - ((cost_price / unit_price) * 100) — not (unit_price - cost_price) / unit_price * 100. The result looks plausible but is mathematically wrong. This is the hardest type of error to catch because the query succeeds and returns numbers.

Fix: Add parentheses around every sub-expression to make the evaluation order explicit: (unit_price - cost_price) / unit_price * 100. Then verify against a known reference value: for Amul Butter with unit_price=56 and cost_price=44, the correct margin is (56-44)/56*100 = 21.4%. Run the query for that specific product and confirm the output matches. Any time a calculated number looks wrong but the query succeeded, check precedence first.

Cause: Floating-point representation error. Numbers like 3.455 cannot be represented exactly in binary floating-point (the same way 1/3 cannot be written as a finite decimal). The actual stored value might be 3.4549999... instead of 3.455, so ROUND to 2 places produces 3.45 instead of 3.46. This happens with FLOAT and DOUBLE columns. DECIMAL columns store values as exact decimal digits and do not have this problem.

Fix: Use DECIMAL data types for all monetary and percentage values — never FLOAT or DOUBLE. For existing FLOAT columns, cast to DECIMAL before rounding: ROUND(CAST(float_column AS DECIMAL(10,4)), 2). If you cannot change the column type, be aware that ROUND on FLOAT values may behave unexpectedly at the boundary and test edge cases explicitly. For financial applications, this distinction is not optional — FLOAT rounding errors in currency accumulate across millions of transactions and cause accounting discrepancies.

Cause: Adding an integer to a date adds days, not months. In PostgreSQL, order_date + 1 adds 1 day. order_date + 30 adds 30 days — not one month. February has 28 or 29 days, months have 28-31 days, so 30 days from January 31 lands in March, not on February 28. If you intend to add one calendar month, adding 30 days is incorrect for dates near the end of a month.

Fix: Use INTERVAL for calendar-aware arithmetic: order_date + INTERVAL '1 month' in PostgreSQL correctly adds one calendar month — January 31 + 1 month = February 28/29 (adjusts to the last day of the month). In MySQL: DATE_ADD(order_date, INTERVAL 1 MONTH). For adding days: order_date + 30 or order_date + INTERVAL '30 days' — both add exactly 30 days and are equivalent. Use integers for day addition, INTERVAL for month/year addition.

The FreshMart procurement team needs a reorder priority report. Write a query on the products table that returns: product_name, category, unit_price, cost_price, a profit column (unit_price minus cost_price), a margin_pct column (profit as a percentage of unit_price, rounded to 1 decimal), a gst_price column (unit_price multiplied by 1.18, rounded to 2 decimal places), and a priority column using CASE: 'Urgent Restock' if out of stock and margin above 25%, 'Restock' if out of stock and margin 25% or below, 'OK' if in stock. Sort by margin_pct descending.