Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Avoid null values

Posted on 2011-03-18
Medium Priority
258 Views
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
Question by:hrvica5
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 29

Expert Comment

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

daletian earned 2000 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

ID: 35165768
ISNULL ( check_expression , replacement_value )
0

## Featured Post

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…