21:52

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Notes about data types

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

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

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

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:

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

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

SELECT 1 MOD 3 AS a, 5 MOD 3 AS b;

though "standard" operator will also work:

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

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

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

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

a	b
1	2

or so

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:

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

At last, if the divider equals zero

Console

Execute

SELECT 1/0 AS a;

MySQL returns NULL, whereas other DBMS considered here return divide by zero error.

Suggested exercises: 137

Oracle PostgreSQL MySQL integer division DIV MOD remainder of division INTEGER

Previous [Float(n)]

[XML Data Type Methods] Next

Last added:

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 DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
month ago

SQL Problems and solutions

S. I. Moiseenko

Notes about data types

Integer division

As for some other DBMS.