13

I'm doing training and one of the scripts has the following command:

SELECT SUM(Col2) FROM clust_table WHERE Col1 % 3 = 1

I would like to know what this snippet is for in the WHERE clause: Col1 % 3 = 1

I did some research on the internet and found no references about this command.

32

It is being used as a Modulo Operator; returning the remainder of a number divided by another.

In your example, the WHERE clause is limiting the results to only those where the Col1 value divided by 3 leaves a remainder of 1. (e.g. 4,7,10, etc.)

| improve this answer | |
25

It's the Modulo operator.

Returns the remainder of one number divided by another.

| improve this answer | |
6

% in SQL can be used in two different formats. If it's used in a string with LIKE then it's a wild card, for example:

WHERE col_name like '%test%'

That would mean find everything in col_name which has the word "test" in it.

But in this specific operation the % symbol is being used as a modulo operator. Modulo (on a calculator normally shown as MOD button) returns the remainder. It can be quite a useful tool if using a for loop to display data and you want to count columns - I have recently used the modulo operator when drawing up a web page. Mine was in PHP, but this is an example:

$count = 0 ;
$records = array () ;
for ( $a = 0 ; $a < 100 ; $a++ )
    $records[$a] = $a ;
foreach ( $records as $record )
{
    if ( $count % 3 == 0 )
        echo '<p>Three items</p>' ;
    if ( $count % 10 == 0 )
        echo '<p>Ten items</p>' ;
    echo '<p>'.$record.'</p>' ;
    $count++ ;
}

This would basically output 1 - 100 and every three items it would output "Three items", and every ten items it would output "Ten Items" because any other numbers would return a value e.g.:

5 / 10 = 0.5 (5 % 10 = 5)
2 / 3 = 0.666... (2 % 3 = 2)
50 / 10 = 5.0 (50 % 10 = 0)
9 / 3 = 3.0 (9 % 3 = 0)
| improve this answer | |
  • 2
    2 % 3 = 2, not 6.. modulo returns the remainder when the number you are dividing by is larger than the number you are dividing. The trailing decimal of the division by decimal has no bearing on the output of mod. – Nick Dewitt Oct 10 '17 at 15:52
  • and select 5 / 10 ; will give 0 in SQL, not 0.5 – ypercubeᵀᴹ Mar 31 '18 at 21:31
2

The other answers are correct. It's the modulo operator, that returns the remainder of the division.

  • 0 % 3 = 0
  • 1 % 3 = 1
  • 2 % 3 = 2
  • 3 % 3 = 0
  • 4 % 3 = 1
  • 5 % 3 = 2
  • ...

But I think it might be helpful to add WHY it is a relevant operation and WHY it might be useful sometimes.

The X % 2 is the most used one because it returns if the number is even or odd.

Imagine that you want to select all columns with even numbers, you can do:

SELECT * FROM table WHERE column % 2 = 0

If you want to select all columns with odd numbers, you can do:

SELECT * FROM table WHERE column % 2 = 1

Other use cases can arise, for instance, you want to filter all columns by their unit, you can use this to get all numbers that have zero in their units:

SELECT * FROM table WHERE column % 10 = 0
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.