Dividing with Integer, Decimal and Float

The division operator in SQL is used to divide one expression or number by another.

Syntax

How Are Integers Divided in SQL?

Let's look at the following example. We run the queries in SQL and Excel to showcase the differences between them.

In the first query , the SQL output for is 2 and not 2.5 as expected like Excel output.

Why?

The division operator only handles the integer part of the output when dividing two integers and the remainder is discarded from the output.

Integer division in SQL does not work as how a division in Excel works.

---

How to Divide Integers and Return Decimal/Float Output?

Method #1: Using CAST()

Example

Method #2: Multiply by 1.0

Example

By multiplying one of the integer or expression with 1.0, it's converted into decimal or floating-point data type.

Method #3: Using ::DECIMAL / :: FLOAT

Example

---

Test Your Knowledge with DataLemur Questions