SQL Numeric Functions and CAST

SQL can do arithmetic directly in a query: calculate margins, apply discounts, round to rupees, and convert between data types. This lesson covers the operators and functions you'll use for nearly every metric.

Arithmetic operators

Common functions

Examples

1. Net amount after discount

Each order line has a discount_pct. Net revenue is quantity * unit_price * (1 - discount_pct / 100):

SELECT order_item_id,
       quantity,
       unit_price,
       discount_pct,
       quantity * unit_price * (1 - discount_pct / 100) AS net_amount
FROM order_items
LIMIT 5;

Result:

(5 rows)

PostgreSQL keeps full precision when it multiplies decimals, hence the long tails on net_amount. That's exactly what ROUND is for.

2. ROUND to make results readable

Margin percentage, rounded to one decimal place:

SELECT product_name,
       ROUND((price - cost_price) / price * 100, 1) AS margin_pct
FROM products
ORDER BY margin_pct DESC
LIMIT 5;

Result:

(5 rows)

3. CEIL and FLOOR

ShopKart packs water bottles 6 per carton. How many cartons do we need for current stock, and how many full cartons can we fill?

SELECT product_name,
       stock_qty,
       CEIL(stock_qty / 6.0) AS cartons_needed,
       FLOOR(stock_qty / 6.0) AS full_cartons,
       stock_qty % 6 AS loose_units
FROM products
WHERE product_id IN (108, 110);

Result:

(2 rows)

4. The integer division trap

When both numbers are integers, PostgreSQL drops the decimals:

SELECT 7 / 2 AS integer_division,
       7 / 2.0 AS decimal_division,
       7::numeric / 2 AS cast_division;

Result:

(1 row)

This bites hardest in ratios. What share of orders were cancelled?

SELECT COUNT(*) FILTER (WHERE status = 'Cancelled') / COUNT(*) AS wrong_ratio,
       ROUND(COUNT(*) FILTER (WHERE status = 'Cancelled') * 100.0 / COUNT(*), 1) AS cancel_pct
FROM orders;

Result:

(1 row)

COUNT returns integers, so 2 / 24 becomes 0. Multiplying by 100.0 first turns the calculation into decimal maths. (FILTER is explained in the aggregate functions lesson.)

5. CAST between types

SELECT CAST('2499' AS INT) + 1 AS text_to_int,
       CAST(23999.75 AS INT) AS numeric_to_int,
       CAST(order_date AS TEXT) AS date_as_text,
       order_date::text AS shortcut
FROM orders
LIMIT 1;

Result:

(1 row)

Note that casting 23999.75 to INT rounds it to 24000 in PostgreSQL rather than chopping off the decimals.

Common mistakes

• Integer division in percentages. Use 100.0 or cast to numeric.
• Rounding too early. Round only in the final SELECT. Rounding each line and then summing gives a slightly different total.
• Using FLOAT for money. Floating-point types store approximations. Use NUMERIC(10,2) for rupees and paise.

Practice question

For each product, show the price, the GST at 18% rounded to whole rupees, and the final price.

SELECT product_name,
       price,
       ROUND(price * 0.18) AS gst,
       price + ROUND(price * 0.18) AS final_price
FROM products
LIMIT 4;

Key takeaways

• Arithmetic works directly in SELECT, WHERE and ORDER BY.
• ROUND, CEIL and FLOOR control decimals.
• Integer ÷ integer drops decimals in PostgreSQL; use 100.0 or ::numeric.
• CAST(x AS type) (or x::type) converts between data types.