SQL - sql Divide by zero error encountered

In recent project, we need to give a top performance statistic and sorted by their target achievement. So in common usually each of the sales rep will be given a target.

So we decided to have something like

SELECT SUM(Sales)/SUM(Target) AS Achievement FROM [sales_table]

In the beginning this statement runs very well because every one has target. But exception case does happen once a while. There are some special agent who has no target for their sales even throughout the whole quarter. So when my reports run it shows ERROR: Divide by zero error encountered. Which I know it the problem caused by the ZERO Target.

In order to rectify the problem, the easiest way is to use SQL internal function to filter those ZERO.

SELECT ISNULL(SUM(Sales)/NULLIF(SUM(target),0),0) FROM [sales_table]

So now those ZERO value will be filtered out and return ZERO.

NULLIF Functions detect all the ZERO value and change it to NULL. When it is NULL, no division will be perform thus we will have NULL result. And ISNULL function detects NULL Value and change it to ZERO. Thus when this query is executed we have a nice statistical chart.