How to get the quotient and remainder of division

Question

I have one employee table which contains:

emp id      Sum
------      ---
1            7
2            6

I want a SQL query for getting the quotient and remainder when dividing the Sum with 8.

Solution 1

Use integer division and mod operators to get the quotient and remainder:

SELECT
    emp_id, 
    sum, 
    sum / 8 AS Result,
    sum div 8 AS Quotient,
    sum mod 8 AS Remainder
FROM employee

emp_id  sum  Result  Quotient  Remainder
1       7    0.8750  0         7
2       6    0.7500  0         6
3       9    1.1250  1         1
4       10   1.2500  1         2
5       11   1.3750  1         3
6       12   1.5000  1         4
7       13   1.6250  1         5
8       14   1.7500  1         6
9       15   1.8750  1         7
10      16   2.0000  2         0

Solution 2

What will be the return type of your qoutient? If you don't care if its a floating point or an integer(whole number). You can try this.

 SELECT 
       (sum / 8) AS qoutient, 
       (sum % 8) AS reminder 
  FROM employee

Solution 3

you can use the % operator to get the remainder. Here's an example.

SELECT Round(17 / 4) -- quotient without decimal   
SELECT 17 % 4 -- remainder

Solution 4

You can use the mysql function DIV to get the qoutient (http://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html#operator_div) :

SELECT 14 DIV 3 

will return 4

It should do the trick

As for the remainder, others have replied

Solution 5

For my PL/SQL function I usually use:

 SELECT trunc(sum/8) --- Quotient
 SELECT mod(sum/8) -- Remainder 

View more solutions