-1
Select ‘contribution amount/rate’= isnull(CAST(priceAmountMax/priceAmountpercetage as Decimal (15,3)),0)
From PriceList

I am sharing a part of my query. I’m using SQL Server. According to my query result I get:

divide by zero error encountered.

How can I edit this query. Thanks for your help.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Zack28
  • 1
  • 2
    What's confusing you about the error? It's fairly clear and a common type of error in any programming language. – Damien_The_Unbeliever Jan 20 '21 at 08:27
  • 2
    Does this answer your question? [How to avoid the "divide by zero" error in SQL?](https://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql) – Dale K Jan 20 '21 at 08:32
  • 1
    Do those funny quotes (`‘`) work in SQL Server? (They give me a syntax error). – Dale K Jan 20 '21 at 08:33

2 Answers2

0

Put NULLIF around priceAmountpercetage, so that the division is against null instead of zero:

isnull(CAST(priceAmountMax / nullif(priceAmountpercetage, 0) as Decimal (15,3)),0)
Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • @GordonLinoff I suspect people getting annoyed about me answering when there is already a duplicate. You're welcome to vote to close the question as duplicate, but the answer, as you say is the correct one – Charlieface Jan 20 '21 at 12:44
-1

You can convert the result to NULL when divider is 0 as follows:

Select case when priceAmountpercetage <> 0 
            then isnull(CAST(priceAmountMax/priceAmountpercetage as Decimal (15,3)),0) 
        end
From PriceList
Popeye
  • 35,427
  • 4
  • 10
  • 31