Solution that displays 0 when SQL division results are decimals in SQL Server

Transferred from: http://blog.sina.com.cn/s/blog_8020e41101019k7t.html

SELECT field1/field2 from TB;

When the value of Field1 > Field2, the result of division is <1, i.e. 0.xxxxxx

This time, in the DB2 environment, the value of select is 0.

How to resolve:

First convert the field1 into a double type, so the calculation will draw a decimal point, will show 0.xxxx

SELECT CAST (field1 as FLOAT)/field2 from TB;

ps. Internet search information, write a double, but in SQL Server2008 has been wrong, changed to float is no problem.

The decimal point shows 4 decimal numbers.

Can be rounded in one step, preserving two decimal places

SELECT ROUND (CAST (field1 as DOUBLE)/field2, 2) from TB;

But because I'm going to use a percentage, it's changed.

SELECT CAST (field1 as FLOAT)/field2 * from TB;

And then add "%" to the page when it's displayed.

If the value of the data column is NULL, set it to 0, then SQL will write

SELECT ROUND (COALESCE (CAST (field1 as DOUBLE), 0)/field2, 2) from TB;

Coalesce This function system uses the following:

A. The input parameter is a character type, and is allowed to be empty, you can use COALESCE (InputParameter, ") to convert null to";

B. Input type is integral type, and allowed to be empty, you can use COALESCE (inputparameter,0), the idling switch to 0;

C. If the input parameter is a character type and is non-empty, you can use COALESCE (InputParameter, ") to convert null to", and then determine whether the return value of the function is ";

D. The input type is integer and is non-null and does not require the use of the COALESCE function to directly use is NULL for a nonempty judgment.

----------------------------------------------------------------

Note:

Cast function for type conversion

The round function is used to control the number of decimal digits

A solution that displays 0 when SQL division results are decimal when you are doing it in server