The division operator in SQL is used to divide one expression or number by another.
Syntax
Let's look at the following example. We run the queries in SQL and Excel to showcase the differences between them.
Query | SQL Output | Excel Output |
---|---|---|
SELECT 10/4 | 2 | 2.5 |
SELECT 10/2 | 5 | 5 |
SELECT 10/6 | 1 | 1.6666666667 |
SELECT 10.0/4 | 2.5000000000000000 | 2.5 |
SELECT 10/3.0 | 3.3333333333333333 | 3.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.
Example
output | output | output | output |
---|---|---|---|
2.5000000000000000 | 2.5 | 1.6666666666666667 | 1.6666666666666667 |
Example
output | output | output | output |
---|---|---|---|
1 | 1.6666666666666667 | 1.0 | 1.6666666666666667 |
By multiplying one of the integer or expression with 1.0, it's converted into decimal or floating-point data type.
Example
output | output | output | output | output | output |
---|---|---|---|---|---|
2.5000000000000000 | 2.5 | 2.5000000000000000 | 2.5 | 1.6666666666666667 | 1.6666666666666667 |
Next Lesson
Aggregate Functions