# Dividing in SQL

can anyone explain to me why this query returns 0.000?

``````SELECT CAST(20/1024 AS NUMERIC(10,3))
``````

I am just trying to do a simple conversion of MB to GB

Thanks

-

You are doing an integer division first and then convert the result. The integer division results in 0. Converting 0 to `NUMERIC(10,3)` results in `0.000`.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

Query 1:

``````SELECT 20/1024;
``````
``````| COLUMN_0 |
------------
|        0 |
``````

To get a non-integer-division you need to convert at least on of the two numbers involved to something other than an integer. In the case of number literals you can just add a `.` after the digits:

Query 2:

``````SELECT 20./1024;
``````
``````| COLUMN_0 |
------------
| 0.019531 |
``````

In the case of variables or columns you need to convert one of them using `CAST` or `CONVERT`:

Query 3:

``````SELECT 20/CAST(1024 AS NUMERIC(10,3));
``````
``````|   COLUMN_0 |
--------------
| 0.01953125 |
``````
-
Ah thanks, I figured it out as above but you have shown me something I didn't know here. – Tom Aug 19 '13 at 14:22

You're using the divide operator to do a integer division.

If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

To get a result with a decimal part, you should first convert one of the operands to a different data type – either `FLOAT` or `NUMERIC` work just fine.

For example (SQL Fiddle):

``````SELECT CAST(CAST(20 AS FLOAT) / 1024 AS NUMERIC(10,3))
``````

Returns `0.02`.

-
Avoid the use of `FLOAT` and `REAL` unless that is explicitly what you want, or you will fall into traps due to floating point approximations. See theregister.co.uk/2006/08/12/floating_point_approximation and docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html (amongst other references) for more information. – David Spillett Jul 27 '15 at 12:34

Actually I fixed it already, its because I had to cast each integer not the whole thing. So this worked:

``````SELECT CAST(CAST(20 AS DECIMAL(2,0))/CAST(1024 AS DECIMAL(4,0)) AS DECIMAL(10,3))
``````
-