Divide by zero error encountered

You may have a request to extract percentages and you encounter this dreaded error, when developing in SQL 2000, SQL 2005 or even SQL 2008.

The workaround for the Divide by zero error encountered. error is by using the NULLIF Function as the the example below demonstrates. The NULLIF function returns the first expression if the two expressions are not equivalent otherwise a NULL is returned.

Usage: SELECT NULLIF(col1, 0)

When col1 = 0 a NULL will be returned.

Example


CREATE TABLE #Products
(
Product VARCHAR(10),
Price DECIMAL(7, 2),
CostPrice DECIMAL(7, 2)
)


INSERT #Products SELECT 'A4 Books', 12.00, 6.00
INSERT #Products SELECT 'Pens', 0.10, 0.05
INSERT #Products SELECT 'Pencils', 0, 0.05


SELECT Product, (CostPrice / Price) * 100 AS PercentProfit FROM #Products


Will Produce the following Error:

(3 row(s) affected)

Server: Msg 8134, Level 16, State 1, Line 14
Divide by zero error encountered.

---------------------------------------------------------------------

SELECT Product, ((CostPrice/NULLIF(Price, 0))*100) AS PercentProfit
FROM #Products

Returns:

Product PercentProfit
---------- -----------------------
A4 Books 50.0000000000
Pens 50.0000000000
Pencils NULL


(3 row(s) affected)

SQL Query to get customers with one product

SQL Query using min and max aggregate function

You may want to find Customer that only has only bought one particular product in the Sales table
You will know the Product_ID is the in the Products table, in this example we use ProductID=10

In this example for each client you will find the lowest value Product_ID and the highest value Product_ID, then filters rows to those where Product_ID is the same as the variable.

--Written by Rishka Booran-Johnson
-- Get Customers with only one product

CREATE TABLE #Customers (
customer_ID int,
firstname varchar(30),
lastname varchar(30)
)

CREATE TABLE #Sales (
Transaction_ID int,
Customer_ID int,
Product_ID int,
Sale_date datetime
)


CREATE TABLE #Products (
Product_ID int,
Product_name varchar(30),
Product_description varchar(50)
)


INSERT #customers SELECT 1, 'john', 'smith'
INSERT #customers SELECT 2, 'sally', 'johnson'
INSERT #customers SELECT 3, 'joe', 'bloggs'



INSERT #Products SELECT 1, 'bicycles', ''
INSERT #Products SELECT 2, 'trains', ''
INSERT #Products SELECT 3, 'dolls', ''



INSERT #Sales SELECT 1, 1, 2, '2009-06-01'
INSERT #Sales SELECT 2, 1, 1, '2009-06-08'
INSERT #Sales SELECT 2, 1, 1, '2009-06-07'
INSERT #Sales SELECT 3, 2, 2, '2009-06-12'
INSERT #Sales SELECT 4, 2, 2, '2009-06-05'
INSERT #Sales SELECT 5, 3, 3, '2009-06-06'
INSERT #Sales SELECT 6, 3, 2, '2009-06-01'
INSERT #Sales SELECT 7, 3, 1, '2009-06-03'


declare
@vproduct_ID int

set @vproduct_ID = 2; -- this would be your input variable for trains


SELECT s.customer_ID, c.firstname, c.lastname, MIN(s.product_ID), MAX(s.product_ID)
FROM #sales s, #customers c
WHERE s.customer_ID = s.customer_ID
AND s.customer_ID = c.customer_ID
GROUP BY s.customer_ID, c.firstname, c.lastname
HAVING MIN(s.product_ID) = @vproduct_ID and MAX(s.product_ID) = @vproduct_ID


DROP TABLE #Customers
DROP TABLE #Sales
DROP TABLE #Products

SQL.. the beginning.

The first time I ever saw a database was in Paradox (database), in 1995. It was a huge learning curve, but I quickly learned what tables, columns and rows were. Then I learned how to build relationships and write SQL queries. (SQL is short for Structured Query Language)

This is how I then figured it was put together.

Every database has collection of one or more tables.
Tables are made up of rows and columns.
The rows contain the data and columns describe the format of the data, namely data type, for that column.

Imagine a telephone directory, information of a person’s first name, surname , address and ph number, this is how the basic table would be constructed:

Table: Phone_Directory

FirstName	LastName	Address	Phone
Jake	Black	123 Round Road, CA	123 321-1234
Sonia	Coke	19 Sailfish Road, WA	232 555-5555
Mel	Brown	32 71st Ave, NY	614 555-5432