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

How to check database growth

Sometimes you’ll want to know how much and how fast your database has been growing. Usually you will have a document that tells you the size information on all the databases, but this is not always the case in the real world. Considering that you perform regular backups, the following script can give you a estimated idea of growth over time of your database.

select
BackupDate = convert(varchar(10),backup_start_date, 111),
SizeInGigs=floor( backup_size/1024000000)
from msdb..backupset
where
database_name = ‘DatabaseName’
and type = ‘d’
order by
backup_start_date desc

Furthermore the Backup type can be used for the following types as well , making it easier to track other types of objects:

• D = Database
• I = Differential database
• L = Log
• F = File or filegroup
• G =Differential file
• P = Partial
• Q = Differential partial

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