SQLite division: integer and floating point division

The operator / stands for integer division:

In the integer division, the remainder is discarded. The result of the integer division is always an integer:

sqlite> select 5/2;
2

If a column is of type INTEGER the division using the operand \ will be integer division and the result will be an integer:

my_integers1|my_integers2|MY_INTEGERS1 / MY_INTEGERS2|
------------|------------|---------------------------|
           1|           1|                          1|
           2|           2|                          1|
           5|           2|                          2|
          10|           3|                          3|
          10|           2|                          5|

If, when dividing, one column if of type REAL, the operand \ will mean floating point division, and the result will be a real number:

my_integers1|my_real2|MY_INTEGERS1 / MY_REAL2|
------------|--------|-----------------------|
           1|     1.0|                    1.0|
           2|     2.0|                    1.0|
           5|     2.0|                    2.5|
          10|     3.0|     3.3333333333333335|
          10|     3.3|     3.0303030303030303|

Another example:

sqlite> select 1/2;
0
sqlite> select 2/3;
0
sqlite> select 3/4;
0

If we wanted floating point division between two integer columns, we can cast one of the columns to REAL as follows:

SELECT MY_INTEGERS1 , MY_INTEGERS2 , CAST(MY_INTEGERS1 as REAL) / MY_INTEGERS2 
from NUMBERS

my_integers1|my_integers2|CAST(MY_INTEGERS1 as REAL) / MY_INTEGERS2|
------------|------------|-----------------------------------------|
           1|           1|                                      1.0|
           2|           2|                                      1.0|
           5|           2|                                      2.5|
          10|           3|                       3.3333333333333335|
          10|           2|                                      5.0|

Division by Zero

In SQLite, division by zero returns NULL:

SELECT MY_INTEGERS1 , MY_INTEGERS2 , MY_INTEGERS1 / MY_INTEGERS2, 
  (MY_INTEGERS1 / MY_INTEGERS2) is null
FROM NUMBERS N

my_integers1|my_integers2|MY_INTEGERS1 / MY_INTEGERS2|(MY_INTEGERS1 / MY_INTEGERS2) is null|
------------|------------|---------------------------|-------------------------------------|
           1|           1|                          1|                                    0|
           2|           2|                          1|                                    0|
           5|           2|                          2|                                    0|
          10|           3|                          3|                                    0|
          10|           2|                          5|                                    0|
          10|           0|                           |                                    1|
          10|            |                           |                                    1|

Remainder

To find the remainder of an integer division, the modulo operator % is used:

SELECT MY_INTEGERS1 , MY_INTEGERS2 , MY_INTEGERS1 / MY_INTEGERS2, MY_INTEGERS1 % MY_INTEGERS2
FROM NUMBERS N

my_integers1|my_integers2|MY_INTEGERS1 / MY_INTEGERS2|MY_INTEGERS1 % MY_INTEGERS2|
------------|------------|---------------------------|---------------------------|
           1|           1|                          1|                          0|
           2|           2|                          1|                          0|
           5|           2|                          2|                          1|
          10|           3|                          3|                          1|
          10|           2|                          5|                          0|
          10|           0|                           |                           |
          10|            |                           |                           |

Another example of the modulo operator:

select 10%4;
2