SQL MOD() function

has average rating 7 out of 10. Total 9 users rated.

<<PreviousNext>>

Description

This SQL MOD() function returns 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

Application of MOD()

In the subsequent pages we have discussed how to apply MOD() with various SQL clauses. For those applications, we have used Oracle 10g Express Edition.

To get remainder of a division of 25 by 7 from the DUAL table, the following sql statement can be used :

SELECT MOD(25,7)
FROM dual;

Output

Sql mod() function with distinct

Sample table : customer

To get unique remainder of a division of 'receive_amt' by 'grade' from the 'customer' table, the following sql statement can be used :

SELECT DISTINCT(MOD(receive_amt,grade))
FROM customer

Output

Sql mod() function with where

Sample table : customer

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 :

SELECT cust_name,opening_amt,receive_amt,
MOD(opening_amt,receive_amt)
FROM customer
WHERE opening_amt > receive_amt
AND MOD(opening_amt,receive_amt)>= 1000;

Output

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.