SQL Academy · Lesson

Integer vs Decimal Division

Avoid surprising results with casting.

Integer vs Decimal Division is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

A Surprising Result

Let's start with a puzzle. What do you think the following query returns?

If you expect 0.5, you may be surprised. In many SQL databases, dividing two integers gives an integer result — dropping the decimal part entirely.

SELECT 1 / 2 AS result;

Integer Division Explained

When both operands in a division are integers, SQL performs integer division. The result is truncated toward zero — no rounding, just the whole number part.

So 1 / 2 = 0, 7 / 3 = 2, and 9 / 4 = 2. The fractional part is silently discarded.

SELECT 7 / 3 AS truncated_result;

More Integer Division Examples

Here are several integer division examples in one query. Notice how all results lose their decimal parts. This behaviour is consistent across expressions involving only integer literals or integer columns.

SELECT
  10 / 4  AS ten_div_four,
  9  / 2  AS nine_div_two,
  1  / 3  AS one_div_three,
  6  / 6  AS six_div_six;

Fixing It with CAST

The most reliable way to force decimal division is to cast at least one operand to a decimal type. CAST(value AS DECIMAL) or CAST(value AS NUMERIC) tells SQL to treat the number as a floating-point value, so the division returns a precise result.

SELECT CAST(1 AS DECIMAL) / 2 AS result;

CAST Both Sides

You only need to cast one side of the division to get a decimal result — SQL will promote the other operand automatically. However, casting both sides makes the intent explicit and avoids confusion when reading the code later.

SELECT
  CAST(7 AS DECIMAL) / CAST(3 AS DECIMAL) AS both_cast,
  CAST(7 AS DECIMAL) / 3                   AS one_cast;

Using a Decimal Literal

Another quick trick is to write one operand as a decimal literal by adding .0. SQL sees 1.0 as a decimal number, which forces decimal arithmetic for the whole expression.

This is handy for quick ad-hoc queries, though CAST is clearer in production code.

SELECT 1.0 / 2  AS decimal_result,
       7.0 / 3  AS another_result;

Real-World Trap: Column Data

The problem really bites when working with integer columns. Imagine a table tracking sales targets. Dividing integer columns produces integer results — a conversion rate of 0 when it should be 0.75!

CREATE TABLE sales_targets (
  rep         TEXT,
  achieved    INT,
  target      INT
);

INSERT INTO sales_targets VALUES
  ('Alice', 75, 100),
  ('Bob',   3,  4),
  ('Carol', 9,  10);

SELECT rep,
       achieved / target AS wrong_rate
FROM   sales_targets;

Fixing the Column Division

Apply CAST to the numerator column and the division now returns the true conversion rate as a decimal. The denominator is automatically promoted.

Multiplying by 100.0 afterwards gives a percentage — still using decimal arithmetic throughout.

SELECT rep,
       CAST(achieved AS DECIMAL) / target          AS conversion_rate,
       CAST(achieved AS DECIMAL) / target * 100.0  AS pct
FROM   sales_targets;

Controlling Decimal Precision

You can control how many decimal places are stored by specifying precision and scale in the cast: DECIMAL(10, 2) means up to 10 digits total, 2 after the decimal point. This is useful when you want neat, rounded output rather than many trailing digits.

SELECT CAST(CAST(1 AS DECIMAL(10,4)) / 3 AS DECIMAL(10,2)) AS rounded_result;

ROUND After Division

Sometimes you want to compute with full precision internally and then round the final display value. The ROUND function takes the value and the number of decimal places you want to keep.

Always divide first (using decimal arithmetic), then round — never round before dividing.

SELECT
  ROUND(CAST(1 AS DECIMAL) / 3, 4)  AS four_places,
  ROUND(CAST(1 AS DECIMAL) / 3, 2)  AS two_places,
  ROUND(CAST(1 AS DECIMAL) / 3, 0)  AS zero_places;

Integer Division Can Be Useful

Integer division is not always a bug — sometimes it is exactly what you need. For example, converting a total number of minutes into whole hours uses integer division intentionally. The key is to choose integer or decimal division deliberately rather than falling into it by accident.

SELECT
  137 / 60  AS full_hours,
  137 % 60  AS remaining_minutes;

Quick Check

Test your understanding of integer vs decimal division in SQL.

Lesson Recap

In this lesson you learned how SQL handles division between integers:

Integer / Integer truncates the result — the decimal part is silently lost.
Use CAST(value AS DECIMAL) on at least one operand to force decimal division.
Writing a decimal literal such as 1.0 also promotes the expression to decimal arithmetic.
Use ROUND after division to control the number of displayed decimal places.
Integer division is sometimes intentional (e.g. converting minutes to hours), but always make it a conscious choice rather than an accident.

Watch out for this trap whenever you divide integer columns — it is one of the most common sources of silent, hard-to-spot bugs in SQL queries.

Frequently asked questions

Is the “Integer vs Decimal Division” lesson free?

Yes — the full text of “Integer vs Decimal Division” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Integer vs Decimal Division”?

Avoid surprising results with casting. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Integer vs Decimal Division” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy