How to Use Round And nullif together?

Question

I have a column that calculates Unit Weight (g) by doing the following calculations

SELECT ROUND (Weight,0) / nullif ([Units per Carton],0) * 454 AS [Unit Weight (g)]
FROM [Item Master]

I used the nullif to eliminate the divide by zero. but i also want to use the Round() on the divisor

i tried this:

SELECT  ROUND (Weight,0) / nullif(Round([Units per Carton],0) * 454) AS [Unit Weight (g)]
FROM [Item Master]

i tried this:

  SELECT  nullif (ROUND (Weight,0) / (Round([Units per Carton],0) * 454) AS [Unit Weight (g)]
    FROM [Item Master]

error: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'AS'.

**So how do i combine a round function with nullif on the divisor?? **

Newly Edited Code

SELECT ROUND (Weight,0) / nullif([Units per Carton],0) * 454 AS [Unit Weight (g)]
FROM [Item Master] 

SELECT ROUND (Weight / NULLIF([Units per Carton],0),0) * 454 AS [Unit Weight (g)]
FROM [Item Master]

I tried the above and the result i got is in this -> picture

The results are different i wanted the result to be 151, 227 and 227 but those results are getting replaced by 0's.

Answer 1

Null should never be the dividend. You will not get logically consistent results dividing by nulls. Remember that a null is not a value at all, it is merely a marker for 'unknown'. (And null is not the same as 'null').

You might try a branch condition instead.

SELECT 454 * ROUND( (Weight * 1.0) / CASE WHEN [Units per Carton] = 0 THEN -1  
ELSE [Units per Carton] END , 0) AS [Unit Weight (g)]

Any resulting weights that are negative are then substitutes for 0.

Or, in a nutshell:

SELECT  @Quotient = CASE WHEN @divisor = 0 THEN NULL
                 ELSE @dividend / @divisor END