SQL MOD() function
MOD() function
SQL MOD() function is used to get the remainder from a division. The SQL DISTINCT command along with the SQL MOD() function is used to retrieve only unique records depending on the specified column or expression.
Syntax:
MOD( dividend, divider )
PostgreSQL and Oracle
All of above platforms support the SQL syntax of MOD().
MySQL Syntax:
MOD(dividend,divider); dividend % divider; dividend MOD divider;
Parameters:
| Name | Description |
|---|---|
| dividend | A number. |
| divider | A number. |
SQL MOD() function: Visual presentation
Example:
To get remainder of a division of 25 by 7 from the DUAL table, the following SQL statement can be used :
-- This SQL statement calculates the modulo operation of dividing 25 by 7 and selects the result from the 'dual' table.
SELECT MOD(25,7)
-- SELECT statement retrieves data from the database
-- MOD() function calculates the modulo operation, which returns the remainder of the division of two numbers
-- In this case, MOD(25,7) calculates the remainder when 25 is divided by 7
FROM dual;
-- Specifies the 'dual' table, a special one-row, one-column table present in Oracle database
-- The 'dual' table is often used for performing calculations or returning single results in SQL queries
Explanation:
- This SQL query is straightforward, as it's only a single statement.
- The purpose of this query is to calculate the modulo operation of dividing 25 by 7.
- MOD() is a mathematical function that calculates the remainder of the division of two numbers.
- In this case, MOD(25,7) will return the remainder when 25 is divided by 7, which is 4.
- The result of the modulo operation, which is 4 in this case, will be selected as the output of the query.
- The 'dual' table is used here because it's a convenient way to execute single-row queries in Oracle SQL without needing to specify an actual table with data.
Output:
MOD(25,7)
----------
4
-- This SQL statement calculates the modulo operation of dividing -25 by 7 and selects the result from the 'dual' table.
SELECT MOD(-25,7)
-- SELECT statement retrieves data from the database
-- MOD() function calculates the modulo operation, which returns the remainder of the division of two numbers
-- In this case, MOD(-25,7) calculates the remainder when -25 is divided by 7
FROM dual;
-- Specifies the 'dual' table, a special one-row, one-column table present in Oracle database
-- The 'dual' table is often used for performing calculations or returning single results in SQL queries
Explanation:
- This SQL query is straightforward, as it's only a single statement.
- The purpose of this query is to calculate the modulo operation of dividing -25 by 7.
- MOD() is a mathematical function that calculates the remainder of the division of two numbers.
- In this case, MOD(-25,7) will return the remainder when -25 is divided by 7, which is 6.
- The result of the modulo operation, which is 6 in this case, will be selected as the output of the query.
- The 'dual' table is used here because it's a convenient way to execute single-row queries in Oracle SQL without needing to specify an actual table with data.
Output:
MOD(-25,7)
----------
-4
-- This SQL statement calculates the modulo operation of dividing 25.4 by 7 and selects the result from the 'dual' table.
SELECT MOD(25.4,7)
-- SELECT statement retrieves data from the database
-- MOD() function calculates the modulo operation, which returns the remainder of the division of two numbers
-- In this case, MOD(25.4,7) calculates the remainder when 25.4 is divided by 7
FROM dual;
-- Specifies the 'dual' table, a special one-row, one-column table present in Oracle database
-- The 'dual' table is often used for performing calculations or returning single results in SQL queries
Explanation:
- This SQL query is straightforward, as it's only a single statement.
- The purpose of this query is to calculate the modulo operation of dividing 25.4 by 7.
- MOD() is a mathematical function that calculates the remainder of the division of two numbers.
- In this case, MOD(25.4,7) will return the remainder when 25.4 is divided by 7, which is approximately 4.4.
- The result of the modulo operation, which is approximately 4.4 in this case, will be selected as the output of the query.
- The 'dual' table is used here because it's a convenient way to execute single-row queries in Oracle SQL without needing to specify an actual table with data.
Output:
MOD(25.4,7)
-----------
4.4
-- This SQL statement calculates the modulo operation of dividing 25.4 by 7.2 and selects the result from the 'dual' table.
SELECT MOD(25.4,7.2)
-- SELECT statement retrieves data from the database
-- MOD() function calculates the modulo operation, which returns the remainder of the division of two numbers
-- In this case, MOD(25.4,7.2) calculates the remainder when 25.4 is divided by 7.2
FROM dual;
-- Specifies the 'dual' table, a special one-row, one-column table present in Oracle database
-- The 'dual' table is often used for performing calculations or returning single results in SQL queries
Explanation:
- This SQL query is straightforward, as it's only a single statement.
- The purpose of this query is to calculate the modulo operation of dividing 25.4 by 7.2.
- MOD() is a mathematical function that calculates the remainder of the division of two numbers.
- In this case, MOD(25.4,7.2) will return the remainder when 25.4 is divided by 7.2.
- The result of the modulo operation will be selected as the output of the query.
- The 'dual' table is used here because it's a convenient way to execute single-row queries in Oracle SQL without needing to specify an actual table with data.
Output:
MOD(25.4,7.2)
-------------
3.8
-- This SQL statement calculates the modulo operation of dividing -25.4 by 7 and selects the result from the 'dual' table.
SELECT MOD(-25.4,7)
-- SELECT statement retrieves data from the database
-- MOD() function calculates the modulo operation, which returns the remainder of the division of two numbers
-- In this case, MOD(-25.4,7) calculates the remainder when -25.4 is divided by 7
FROM dual;
-- Specifies the 'dual' table, a special one-row, one-column table present in Oracle database
-- The 'dual' table is often used for performing calculations or returning single results in SQL queries
Explanation:
- This SQL query is straightforward, as it's only a single statement.
- The purpose of this query is to calculate the modulo operation of dividing -25.4 by 7.
- MOD() is a mathematical function that calculates the remainder of the division of two numbers.
- In this case, MOD(-25.4,7) will return the remainder when -25.4 is divided by 7, which is approximately 6.6.
- The result of the modulo operation, which is approximately 6.6 in this case, will be selected as the output of the query.
- The 'dual' table is used here because it's a convenient way to execute single-row queries in Oracle SQL without needing to specify an actual table with data.
Output:
MOD(-25.4,7)
------------
-4.4
-- This SQL statement attempts to calculate the modulo operation of dividing 25 by 0 and selects the result from the 'dual' table.
SELECT MOD(25,0)
-- SELECT statement retrieves data from the database
-- MOD() function calculates the modulo operation, which returns the remainder of the division of two numbers
-- In this case, MOD(25,0) attempts to calculate the remainder when 25 is divided by 0
FROM dual;
-- Specifies the 'dual' table, a special one-row, one-column table present in Oracle database
-- The 'dual' table is often used for performing calculations or returning single results in SQL queries
Explanation:
- This SQL query is straightforward, as it's only a single statement.
- The purpose of this query is to calculate the modulo operation of dividing 25 by 0.
- MOD() is a mathematical function that calculates the remainder of the division of two numbers.
- However, division by zero is undefined in mathematics, including in SQL, which results in an error.
- When attempting to execute MOD(25,0), an error will occur because division by zero is not allowed.
- The 'dual' table is used here because it's a convenient way to execute single-row queries in Oracle SQL without needing to specify an actual table with data.
Output:
MOD(25,0)
----------
25;
SQL MOD() function with distinct
Sample table: customer+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
To get unique remainder of a division of 'receive_amt' by 'grade' from the 'customer' table, the following SQL statement can be used :
-- This SQL statement calculates the modulo operation of dividing the 'receive_amt' column by the 'grade' column for each row in the 'customer' table, selecting distinct results.
SELECT DISTINCT(MOD(receive_amt,grade))
-- SELECT statement retrieves data from the database
-- DISTINCT keyword ensures that only unique values are returned
-- MOD() function calculates the modulo operation, which returns the remainder of the division of two numbers
-- In this case, MOD(receive_amt,grade) calculates the remainder when 'receive_amt' is divided by 'grade'
FROM customer;
-- Specifies the 'customer' table from which the data is being retrieved
Explanation:
- This SQL query is designed to calculate the modulo operation of dividing the 'receive_amt' column by the 'grade' column for each row in the 'customer' table.
- The DISTINCT keyword ensures that only unique modulo operation results are returned, preventing duplicates.
- MOD() is a mathematical function that calculates the remainder of the division of two numbers.
- In this case, MOD(receive_amt,grade) calculates the remainder when the value in the 'receive_amt' column is divided by the value in the 'grade' column for each row.
- The query is executed against the 'customer' table to retrieve the required data.
Output:
(MOD(RECEIVE_AMT,GRADE))
------------------------
1
2
6000
0
SQL MOD() function with where
Sample table: customer+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
To get data of 'cust_name', 'opening_amt', 'receive_amt' and remainder of the division of 'opening_amt' by 'receive_amt' from the 'customer' table with following conditions -
1. 'opening_amt' must be more than 'receive_amt',
2. remainder of the division of 'opening_amt' by 'receive_amt' must be more than or equal to 1000,
the following SQL statement can be used :
-- This SQL statement selects the customer name, opening amount, receive amount, and the modulo operation result of dividing opening amount by receive amount for each row in the 'customer' table, where opening amount is greater than receive amount and the modulo operation result is greater than or equal to 1000.
SELECT cust_name, opening_amt, receive_amt,
MOD(opening_amt, receive_amt)
-- SELECT statement retrieves data from the database
-- cust_name, opening_amt, and receive_amt are columns selected for output
-- MOD() function calculates the modulo operation, which returns the remainder of the division of two numbers
-- In this case, MOD(opening_amt, receive_amt) calculates the remainder when opening_amt is divided by receive_amt
FROM customer
-- Specifies the 'customer' table from which the data is being retrieved
WHERE opening_amt > receive_amt
-- WHERE clause filters the rows based on a condition
-- It selects only those rows where the opening_amt is greater than the receive_amt
AND MOD(opening_amt, receive_amt) >= 1000;
-- This condition further filters the rows by checking if the modulo operation result of opening_amt divided by receive_amt is greater than or equal to 1000
Explanation:
- This SQL query is designed to retrieve specific information from the 'customer' table based on certain conditions.
- The SELECT statement specifies the columns that will be included in the output: cust_name, opening_amt, receive_amt, and the result of the modulo operation.
- The WHERE clause filters the rows from the 'customer' table based on two conditions:
- It selects only those rows where the opening amount (opening_amt) is greater than the receive amount (receive_amt).
- Additionally, it further filters the rows by checking if the modulo operation result of opening amount divided by receive amount is greater than or equal to 1000.
- The modulo operation (MOD()) calculates the remainder when the opening amount is divided by the receive amount for each row.
- The query is executed against the 'customer' table to retrieve the required data.
Output:
CUST_NAME OPENING_AMT RECEIVE_AMT MOD(OPENING_AMT,RECEIVE_AMT) ---------------------------------------- ----------- ----------- ---------------------------- Holmes 6000 5000 1000 Yearannaidu 8000 7000 1000 Shilton 10000 7000 3000 Charles 6000 4000 2000 Martin 8000 7000 1000 Ramesh 8000 7000 1000
Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.
Here is a slide presentation which covers the SQL arithmetic functions.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.