Divided by zero encountered in SQL

Question

So, I have a query where I am able to get some values using some formulas and that kind of stuff. When the query have values it will work fine, but if I have no values, in the divided formulas it will give me the know error

Divided by zero encountered

This is the query:

WITH TrashFile AS(
SELECT FileType AS[Extension],
COUNT(*) AS [Nº of files],
CAST(((COUNT(FileSize) * 100.0) / (SELECT COUNT(*) FROM InfoFile))
AS DECIMAL(10, 2)) AS[Percentage(%)],
CAST((SUM(FileSize) / 1024.0) AS DECIMAL(10,1)) AS [Total(KB)],
NULL AS [Converted to MB],
NULL AS [Converted to GB],
MIN(COUNT(*)) OVER() * 100.0 / (SUM(COUNT(*)) OVER()) AS[Min.Percentage(%)],
MAX(COUNT(*)) OVER() * 100.0 / SUM(COUNT(*)) OVER() AS[Max.Percentage(%)]
FROM InfoFile
GROUP BY FileType)
SELECT[Extension],
[Nº of files],
[Percentage(%)],
[Total(KB)],
[Converted to MB],
[Converted to GB],
NULL AS[Min.Percentage(%)],
NULL AS[Max.Percentage(%)]
FROM TrashFile
UNION ALL
SELECT '-----------------------------',
COUNT('Nº de extensions'),
((COUNT(FileType) * 100) / (SELECT COUNT(FileType) FROM InfoFile)),
CAST((SUM(FileType) / 1024.0) AS DECIMAL(10,1)),
CAST((SUM(FileType) / 1024.0 / 1024.0) AS DECIMAL(10,5)),
CAST((SUM(FileType) / 1024.0 / 1024.0 / 1024.0) AS DECIMAL(10,9)),
CAST(((SELECT MAX([Min.Percentage(%)]) FROM TrashFile)) 
AS DECIMAL(10,2)) AS[Min.Percentage(%)] ,
CAST((SELECT MAX([Max.Percentage(%)]) FROM TrashFile) 
AS DECIMAL(10,2)) AS[Max.Percentage(%)]
FROM InfoFile

Do you have any idea how I can solve it?

You can easily Google for an answer to this question, including many here on SO itself, like this one: [How to avoid the "divide by zero" error in SQL?](http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql). — 3N1GM4, Dec 12 '16 at 16:12

score 1 · Answer 1 · answered Dec 12 '16 at 16:12

1

When dealing with similar situation, I insert all my divides into a case statement:

Case when [denominator] > 0 then value/denominator else 0 end as result.

answered Dec 12 '16 at 16:12

Ben

485
9
19

3

This answer is mathematically incorrect. You shouldn't return a zero where the answer is undefined unless you have a very compelling reason to do so, and even then you're possibly looking at a data or logic problem earlier in the chain. Also, I find it odd to return a zero for all negative denominators. `numerator / nullif(denominator, 0)` is one of the more elegant (and correct) solutions to this problem in SQL Server. – Tim Lehner Dec 12 '16 at 17:05

iamdave · Answer 2 · 2016-12-12T16:52:01.317

Your query will be falling over when one of your SELECT COUNT(*) sub-selects returns 0 records.

There are two ways to handle this:

Change your query/aggregations so that you never return 0 records in your COUNT()s
Use a ISNULL and NULLIF to trap and handle your divide by 0s:

ISNULL(MAX(COUNT(*)) OVER() * 100.0 / NULLIF(SUM(COUNT(*)) OVER(),0) ,0)

This works because dividing any number by NULL returns NULL, which means you have just avoided a Divide by Zero error. You can then replace this NULL with whatever you want to show instead. In the example above, it will return a 0. If you wanted to substitute something else to be handled later on substitute it into <value> below:

ISNULL(MAX(COUNT(*)) OVER() * 100.0 / NULLIF(SUM(COUNT(*)) OVER(),0) ,<value>)

Good answer, but I don't think that `SUM(COUNT(*)) OVER ()` can return `0` if there is a `GROUP BY` clause in the query. — Gordon Linoff, Dec 12 '16 at 16:19

score 0 · Answer 3 · answered Dec 12 '16 at 16:18

You can use the CASE WHEN statement in order to avoid the division by zero.

SELECT FileType AS[Extension],
COUNT(*) AS [Nº of files],

CASE WHEN (SELECT COUNT(*) FROM InfoFile) > 0
THEN
   CAST(((COUNT(FileSize) * 100.0) / (SELECT COUNT(*) FROM InfoFile))
ELSE 
   NULL
END AS Alias,
... FROM Table

Bob Jarvis - Слава Україні · Answer 4 · 2016-12-12T19:53:55.273

There are a number of places in this statement where you're dividing by the results of a COUNT(*) or similar value. These COUNT values might be zero which would cause a DIVIDE BY ZERO error to occur. One way to work around this is to using the NULLIF function, which will replace the zero values with NULL. This will result in the computations returning NULL instead of a DIVIDE BY ZERO error - if the NULL values are not what you want you can surround the computations with ISNULL and replace the NULLs with whatever value you like.

WITH TrashFile AS(SELECT FileType AS [Extension],
                         COUNT(*) AS [Nº of files],
                         CAST(((COUNT(FileSize) * 100.0) / (SELECT NULLIF(COUNT(*), 0)
                                                              FROM InfoFile))
                                     AS DECIMAL(10, 2)) AS [Percentage(%)],
                         CAST((SUM(FileSize) / 1024.0) AS DECIMAL(10,1)) AS [Total(KB)],
                         NULL AS [Converted to MB],
                         NULL AS [Converted to GB],
                         MIN(COUNT(*)) OVER() * 100.0 / NULLIF(SUM(COUNT(*)), 0) OVER()) AS [Min.Percentage(%)],
                         MAX(COUNT(*)) OVER() * 100.0 / NULLIF(SUM(COUNT(*)), 0) OVER()) AS [Max.Percentage(%)]
                    FROM InfoFile
                    GROUP BY FileType)
SELECT [Extension],
       [Nº of files],
       [Percentage(%)],
       [Total(KB)],
       [Converted to MB],
       [Converted to GB],
       NULL AS [Min.Percentage(%)],
       NULL AS [Max.Percentage(%)]
  FROM TrashFile
UNION ALL
SELECT '-----------------------------',
       COUNT('Nº de extensions'),
       ((COUNT(FileType) * 100) / (SELECT NULLIF(COUNT(FileType), 0) FROM InfoFile)),
       CAST((SUM(FileType) / 1024.0) AS DECIMAL(10,1)),
       CAST((SUM(FileType) / 1024.0 / 1024.0) AS DECIMAL(10,5)),
       CAST((SUM(FileType) / 1024.0 / 1024.0 / 1024.0) AS DECIMAL(10,9)),
       CAST(((SELECT MAX([Min.Percentage(%)]) FROM TrashFile)) 
            AS DECIMAL(10,2)) AS[Min.Percentage(%)] ,
       CAST((SELECT MAX([Max.Percentage(%)]) FROM TrashFile) 
            AS DECIMAL(10,2)) AS[Max.Percentage(%)]
  FROM InfoFile

The SUM function requires 1 argument(s) in the `MIN(COUNT(*)) OVER() * 100.0 / (NULLIF(SUM(COUNT(*), 0)) OVER()) AS [Min.Percentage(%)],` — LeugimSnitram, Dec 12 '16 at 16:50

Divided by zero encountered in SQL

4 Answers4