logo

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.

QuerySQL OutputExcel Output
SELECT 10/422.5
SELECT 10/255
SELECT 10/611.6666666667
SELECT 10.0/42.50000000000000002.5
SELECT 10/3.03.33333333333333333.333333333

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


outputoutputoutputoutput
2.50000000000000002.51.66666666666666671.6666666666666667

Method #2: Multiply by 1.0

Example


outputoutputoutputoutput
11.66666666666666671.01.6666666666666667

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


outputoutputoutputoutputoutputoutput
2.50000000000000002.52.50000000000000002.51.66666666666666671.6666666666666667

Test Your Knowledge with DataLemur Questions




Next Lesson

Aggregate Functions