MySQL allows you to divide integer or floating point numbers by using the division operator (/) or the integer division DIV operator.
Here’s an example of dividing a number using the division operator:
SELECT 15 / 3;
The SQL query above will produce the following result set:
+--------+
| 15 / 3 |
+--------+
| 5.0000 |
+--------+
1 row in set (0.00 sec)
The division operator returns a floating number even when the division result is an integer.
To remove the fractional point, you need to use the DIV operator as follows:
SELECT 15 DIV 3;
The query above will return the following output:
+----------+
| 15 DIV 3 |
+----------+
| 5 |
+----------+
As you can see, the DIV operator returns only a round number.
When you perform a division that results in a floating number, the DIV operator simply removes the fractional point without rounding the number up or down.
For example, dividing 14 by 3 will produce the following output:
mysql> SELECT 14 / 3, 14 DIV 3;
+--------+----------+
| 14 / 3 | 14 DIV 3 |
+--------+----------+
| 4.6667 | 4 |
+--------+----------+
Although the result is 4.6, the DIV operator doesn’t round the number up. The fractional point is simply removed from the number.
And that’s how you can divide numbers in MySQL. You can pass a column with numbers data type to the left or right side of the operator to perform a division using the column’s value.
Suppose you have a score column in the students table as follows:
mysql> SELECT score FROM students;
+-------+
| score |
+-------+
| 7 |
| 8 |
| NULL |
| 8 |
| NULL |
| 6 |
| 8 |
+-------+
Here’s how to use the column for division:
SELECT score / 2, 80 / score FROM students;
The query above will return the following result set:
+-----------+--------------------+
| score / 2 | 80 / score |
+-----------+--------------------+
| 3.5 | 11.428571428571429 |
| 4 | 10 |
| NULL | NULL |
| 4 | 10 |
| NULL | NULL |
| 3 | 13.333333333333334 |
| 4 | 10 |
+-----------+--------------------+
You can also use the DIV operator:
SELECT score DIV 2, 80 DIV score FROM students;
That produces the following output:
+-------------+--------------+
| score DIV 2 | 80 DIV score |
+-------------+--------------+
| 3 | 11 |
| 4 | 10 |
| NULL | NULL |
| 4 | 10 |
| NULL | NULL |
| 3 | 13 |
| 4 | 10 |
+-------------+--------------+
You’ve learned how to perform numbers division in MySQL. Great work! 👍
