How to prevent divide 0 error in SQL Server

Let suppose you have this Sales table from which you want to calculate the ratio between Income and number of Visits.

You can use the following query to generate the correct result:

CREATE TABLE Sales(
[ID] INT NOT NULL,
[Income] INT NULL,
[Visits] INT NULL
);

INSERT INTO Sales([ID],[Income],[Visits])
VALUES
(1,1200,30),
(2,0,22),
(3,800,1),
(4,749,7),
(5,192,0),
(6,974,10);

If we simply write a query with [Income]/[Visits] we will get an error, as ID=5 row has 0 in the Visits column.

SELECT [ID],[Income]/[Visits]
FROM Sales

Msg 8134 Level 16 State 1 Line 1
Divide by zero error encountered.

To prevent this error, it is a best practice to use the NULLIF function that will output NULL when a zero will be found as denominator.

SELECT [ID],[Income]/NULLIF([Visits],0)
FROM Sales

You can find an interactive version of this example following this link .