loading..
   English
13:31

Integer division

Newbies in  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server sometimes fall in bewilderment when getting the results of queries like this

Console
Execute
  1. SELECT 1/3 AS a, 5/3 AS b;

Somebody (I suspect, they are users of MySQL or Oracle) expects the results in somewhat as 

a b
0.3333 1.6667
i.e. real number, the others -

a b
0 2
i.e. the rounding to the nearest integer, whereas SQL Server gives

a b
0 1

To resolve this bewilderment I shall tell, that operation "/" designates just the integer part of the result of dividing two integers  (namely, quotient) if both operands are integers. I.e. the separate designation for this operation is not supplied, and the symbol of "usual division" is used. If you wish to receive decimal number you need convert at least one operand to real data type explicitly (the first column) or implicitly (the second column):

Console
Execute
  1. SELECT CAST(1 AS DEC(12,4))/3 AS a, 5./3 AS b;

a b
0.333333 1.666666

Modulo operation is designated as "%" in SQL Server:

Console
Execute
  1. SELECT 1 % 3 AS a, 5 % 3 AS b;

a b
1 2

As for some other DBMS.

PostgreSQL behaves similarly SQL Server.

MySQL has a special operator DIV for obtaining a quotient:

  1. SELECT 1 DIV 3 AS a, 5 DIV 3 AS b;

The remainder of division can be received also a la Pascal:

  1. SELECT 1 MOD 3 AS a, 5 MOD 3 AS b;
though "standard" operator will also work:

  1. SELECT 1 % 3 AS a, 5 % 3 AS b;

Oracle has not operator for getting quotient in general, so the division result

  1. SELECT 1/3 AS a, 5/3 AS b FROM dual;

a b
.333333333333333 1.66666666666667
you have to convert to needed integer, for example, so

  1. SELECT CEIL(1/3) AS a, CEIL(5/3) AS b FROM dual;

a b
1 2
or so

  1. SELECT FLOOR(1/3) AS a, FLOOR(5/3) AS b FROM dual;

a b
0 1

MOD function is used in Oracle for getting  remainder of division:

  1. SELECT MOD(1,3) AS a, MOD(5,3) AS b FROM dual;

At last, if the divider equals zero

Console
Execute
  1. SELECT 1/0 AS a;
MySQL returns NULL, whereas other DBMS considered here return divide by zero error.

Suggested exercises: 137

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100