CODEWITHSUNDEEP
sqlsql-server
TechGuy
TechGuy

Reputation: 4568

Take Avg value as a 0 when Divide it by zero

I have two database fields(SQL Server) those fields are decimal. Both of those fields are 0 in SQL Query I need to take percentage of those values. In this record shows Error on database. But if this happens I need to show 0 value.

There's a scenario TotalSalesPrice = 0.00 & CurrNewsPrice= 0 Then the Avgnews formular is AvgNews = (CurrNewsPrice / TotalSalesPrice) * 100

Select  TotalSalesPrice,CurrNewsPrice,CAST(CurrNewsPrice AS decimal(18,2)) / CAST(TotalSalesPrice AS decimal(18,2)) * 100.00 AS AvgNews
from [dbo].[N_NewsLine]

Upvotes: 2

Views: 134

Answers (6)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

If you want the results to be decimal(18, 2) then do the cast() after the division. The rules for the result type when dividing decimals is . . . rather Byzantine (no offense intended to Byzantines).

I would recommend NULLIF() to avoid the divide-by-zero, resulting in:

Select TotalSalesPrice, CurrNewsPrice,
       cast(CurrNewsPrice * 100.00 /
            nullif(TotalSalesPrice, 0) as decimal(18, 2)
           ) as AvgNews
from [dbo].[N_NewsLine]

Upvotes: 0

Deep patel
Deep patel

Reputation: 136

Select TotalSalesPrice,CurrNewsPrice,CAST(CurrNewsPrice AS decimal(18,2)) / CAST(NULLIF(TotalSalesPrice,0) AS decimal(18,2)) * 100.00 AS AvgNews from [dbo].[N_NewsLine]

Upvotes: 0

Joey.Zhang
Joey.Zhang

Reputation: 1

use case...when.. before the calculation

Select  TotalSalesPrice,CurrNewsPrice,
CAST( Case CAST(TotalSalesPrice AS decimal(18,2))
when 0 then 0.00
else CAST(CurrNewsPrice AS decimal(18,2)) / CAST(TotalSalesPrice AS decimal(18,2)) * 100.00 end AS decimal(18,2)) AS AvgNews
from [dbo].[N_NewsLine]

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062865

special case it?

select  TotalSalesPrice,CurrNewsPrice, case
    when TotalSalesPrice = 0 and CurrNewsPrice = 0 then 0
    else CAST(CurrNewsPrice AS decimal(18,2)) /
          CAST(TotalSalesPrice AS decimal(18,2)) * 100.00
    end AS AvgNews
from [dbo].[N_NewsLine]

Upvotes: 4

Amit Kumar
Amit Kumar

Reputation: 5962

You can check it before using those values.

Select  TotalSalesPrice,CurrNewsPrice,IIF(TotalSalesPrice = 0, 
                                            0,
                                            CAST(CurrNewsPrice AS decimal(18,2)) / CAST(TotalSalesPrice AS decimal(18,2)) * 100.00)  AS AvgNews
from [dbo].[N_NewsLine]

Upvotes: 2

Squirrel
Squirrel

Reputation: 24763

use NULLIF() on the denominator. And ISNULL() on the result of the calculation

Select TotalSalesPrice,
       CurrNewsPrice,
       ISNULL(CAST(CurrNewsPrice AS decimal(18,2)) 
            / CAST(NULLIF(TotalSalesPrice, 0) AS decimal(18,2)) * 100.00, 0) AS AvgNews
from [dbo].[N_NewsLine]

Upvotes: 3

Related Questions