CODEWITHSUNDEEP
sqlsql-serversum
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Division in SQL Server

I am trying to use division in SQL Server. I have tried the code snippet below.

    SELECT m.[Profit] / f.[Target]

And I have also tried 

    SELECT SUM(m.[Profit] / f.[Target])

but I get the error

Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

What is the reason for this and how can I fix it? Suggested fixes online say that this code should work but it doesn't.

Thanks

Upvotes: 0

Views: 673

Answers (3)

cclarke
cclarke

Reputation: 86

If I were trying to determine the % of a goal (inferred by profit /target columns), I would try something like:

  SELECT 
    CASE WHEN ISNULL(f.[Target],0) = 0 THEN  NULL -- or whatever you need to return when the target value is undefined, or it is 0
    ELSE  m.[Profit] / f.[Target] END AS [result]

Upvotes: 1

Crispy Holiday
Crispy Holiday

Reputation: 472

The error you get specifies exactly what is the problem. Your code is trying to divide by zero, which is mathematically impossible.

Please NULLIF to avoid this error.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Use NULLIF to avoid divided by zero exception 

SELECT SUM(m.[Profit] / NULLIF(f.[Target],0))

When denominator is zero then it will be replaced with NULL

Upvotes: 3

Related Questions