Why am I getting zero in specific rows as a result of this division?

Question

I am using SQL Server 2014 and I have the following syntax in my query to compute the result of a division.

USE MyDatabase

SELECT 
    ...
    CAST(cte1.[F&B] AS FLOAT) * CAST (cte1.[RN] / cte1.[Total RN] AS FLOAT) AS [F&B_Split],
    CAST(cte1.[F&B] AS FLOAT) * CAST (cte1.[RN] / cte1.[Total RN] AS FLOAT) / CAST(cte1.[GN] AS FLOAT) AS [F&B_Split_PerGN]
    ...
FROM 
    table1

Columns RN and GN in table1 are integers and Column F&B is of numeric type.

Here is an extract of my results:

RN  TOTALRN    GN    F&B    F&B_Split      F&B_Split_PerGN
-------------------------------------------------------------
 9    9        18    1200     1200         66.6666666666667
 9    10       18    3900        0          0

The calculations appear to work correctly when both RN and TotalRN values are the same (row 1 above) but gives 0 for F&B_Split and F&B_Split_PerGN (row 2 above).

What am I doing wrong here?

Guessing a bit here, but perhaps cast those `RN` and `Total RN` to Float before dividing them into eachother. I suspect that division is resulting in your `0` and casting `0` to float isn't helping. — JNevill, May 22 '19 at 19:55
SQL server integer math. basically an int can't store decimal values so convert to numeric with specific precision & Scale. To prove it's a problem just multiple rn and totalRN by 1.0 and I bet the 0's go away See: https://stackoverflow.com/questions/11112462/what-is-wrong-with-this-sql-server-query-division-calculation — xQbert, May 22 '19 at 20:06

score 0 · Accepted Answer · answered May 22 '19 at 20:13

You care about the type for the division. I would remove the cast to float. But that is your choice.

For me, the simplest solution is to in a multiplication by 1.0 before the division:

CAST(cte1.[F&B] AS FLOAT) * CAST (cte1.[RN] * 1.0 / cte1.[Total RN] AS FLOAT) AS [F&B_Split],
CAST(cte1.[F&B] AS FLOAT) * CAST (cte1.[RN] * 1.0 / cte1.[Total RN] AS FLOAT) / CAST(cte1.[GN] AS FLOAT) AS [F&B_Split_PerGN]

Or:

cte1.[F&B] * cte1.[RN] * 1.0 / cte1.[Total RN] AS [F&B_Split],
cte1.[F&B]  * cte1.[RN] * 1.0 / cte1.[Total RN]  AS [F&B_Split_PerGN]

If you want a particular type for the expression, the convert the entire expression:

convert(float, cte1.[F&B] * cte1.[RN] * 1.0 / cte1.[Total RN]) AS [F&B_Split],
convert(float, cte1.[F&B]  * cte1.[RN] * 1.0 / cte1.[Total RN]) AS [F&B_Split_PerGN]

Why am I getting zero in specific rows as a result of this division?

1 Answers1