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)

Sequence number in MSSQL

Today someone asked me how to place a sequential number as a primary key value in a table. He did not want to write a loop in his procedure to increment the primary key value, just a 'plain and simple insert statement' with the table doing the calculations.

In Oracle, the SEQUENCE is the object place holder to use on a table trigger event.

In MSSQL, INCREMENT is the property and can be used as follows:

-------------------------------------------------------------------------
/*
To test this, first set up your table, with the IDENTITY property,
followed with two variables (starting int , increment by value),
in this example the ProductID starts at one, and every new record
will increase the Product counter ID by 1.
*/

CREATE TABLE product
(productID INT IDENTITY(1,1) ,
productDescription VARCHAR(20),
CONSTRAINT ID_PK PRIMARY KEY (productID)
)



/*
Insert some data into the table
*/
insert into product (productDescription) values ('bicycles')
insert into product (productDescription) values ('cars')
insert into product (productDescription) values ('trains')
insert into product (productDescription) values ('buses')
insert into product (productDescription) values ('trams')


/*
Select the data, to test the increment
*/

select * from product

/*
Drop test table
*/
drop table product

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

I hope this article can you, and as always I welcome feedback on this article.

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