Avoid null values

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.
hrvica5Asked:
Who is Participating?
 
daletianConnect With a Mentor Commented:
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
 
sammySeltzerCommented:
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
 
BlakeRogersCommented:
ISNULL ( check_expression , replacement_value )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.