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.

33

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.)

26

It's the Modulo operator.

Returns the remainder of one number divided by another.

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)
  • 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

protected by ypercubeᵀᴹ Oct 11 '17 at 11:42

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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