Solved

Avoid null values

Posted on 2011-03-18
3
251 Views
Last Modified: 2012-05-11
Hi,

I have databes with

AA           BB              CC          DD
5               6                 7              0

but sometimes i have this:
AA           BB              CC          DD
5               6                 0              8

I have to do this (AA+BB+CC+DD)/100

I want that SQL exclude 0 or null during dividing.

1. (AA+BB+CC)/100
2. (AA+BB+DD)/100

Thx.
0
Comment
Question by:hrvica5
3 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 35165155
You can try this:

Select (AA+BB+CC) / nullif(100, 0)

OR

use CASE statement to avoid null:

Select Case when divisor=0 then null Else (AA+BB+CC) / divisor End ,,,

In your case, 100 is divisor.

The first example is neater though
0
 
LVL 4

Accepted Solution

by:
daletian earned 500 total points
ID: 35165451
To exclude NULL, u can use the ISNULL function to substitue NULL values with what u specify

eg.ISNULL(AA, 0)  + ISNULL(BB, 0)  + ISNULL(CC, 0)  + ISNULL(DD, 0)

ISNULL(DD, 0)  wil give 0 if DD has a  NULL value

In your case, since you are dividing by a finite number, u don't need to worry about the 0s

0
 

Expert Comment

by:BlakeRogers
ID: 35165768
ISNULL ( check_expression , replacement_value )
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question