Division ( / ) not giving my answer in postgresql

Saying goodbye to truncation

When integers chat, the conversation often gets truncated during division. Want to gatecrash and change the topic? Cast one operand to numeric or a float variant using :::

SELECT (revenue::NUMERIC / cost) AS profit_ratio;


Now, profit_ratio retains its decimal part. Divide dev_cost and sell_cost, both integers, and round up? Ace it with:

-- Everybody hates leftovers
SELECT CEIL(dev_cost::NUMERIC / sell_cost) AS rounded_profit_ratio;


Rounding: choose your preference

When it comes to rounding, everyone has an opinion. Want to always round it up? CEIL() or CEILING() is your new best friend. But it's always nice to be in control. Need precision? Say hello to ROUND() or TRUNC():

-- Because who needs extra decimal places?
SELECT ROUND(profit_margin::NUMERIC, 2) AS rounded_margin FROM ledger;


Choose the right data type: know your business

Never underestimate the power of data types in the world of divsion. Manoeuvring financial transactions or scientific calculations? numeric or decimal types pull out the big guns of precision. Here’s a mini guide to help you navigate:

• Integer types (INT, SMALLINT): Fast but may cut things short.
• Floating-point (REAL, DOUBLE PRECISION): Prefer an estimate? They are your go-to.
• Numeric (NUMERIC, DECIMAL): Perfect for number-crunching where every digit counts.

Beware the rabbit holes

Here are some potential missteps to watch out for in your SQL trek:

• Implicit type casting: When two integers hang out, the result is alas, an integer. Always cast explicitly to maintain peace.
• Precision loss: Be preppared to fight the battle of rounding and precision loss. Armor up with the right data types.
• The zero monster: Watch for zero in the divisor to avoid dreaded errors. Conditionals or the superhero NULLIF() can handle this:

-- Zero monsters aren't scary anymore
SELECT sales / NULLIF(cost, 0) AS sales_ratio;


Accurate queries: a non-negotiable

The accuracy of queries can make or break results, especially in high-stake domains like finance or scientific computing. A cross-check on data types and judicious use of rounding or numeric precision can help you hit the bull's eye.