addition of a value to a null value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

addition of a value to a null value

Author

Topic

amalshah71
Starting Member

9 Posts

Posted - 2006-12-06 : 06:20:59

i have the idea that 23+null=null

now following is my case with 2 columns as:

salary bonus
1 null
2 5
3 null

i have to find the total salary(i.e. salary+bonus)

this is how i managed to do it:

select empname,(salary + commission) as 'total salary'
from emp
where commission is not null
union
select empname,salary from emp
where commission is null

now my question is,wht if the case now is different as below:

salary bonus hra
1 null 6
2 5 null
3 null null

now how do i calculate total salary(i.e. salary+bonus+hra)

am a beginner
help appreciated

cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-06 : 06:25:26

select isnull(salary, 0) + isnull(bonus, 0)
from yourtablenamehere

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-06 : 06:27:22

quote:
Originally posted by Peso

select isnull(salary, 0) + isnull(bonus, 0) + isnull(hra, 0)
from yourtablenamehere

Peter Larsson
Helsingborg, Sweden

Harsh Athalye
India.
"Nothing is Impossible"

amalshah71
Starting Member

9 Posts

Posted - 2006-12-06 : 06:41:54

thnk u harsh,peso.....that helped and made me understand...

before this i had never heard of a function isnull(col_name,something)

i could infer that 0 replaces a data value where it encounters null

it would be helpful if i get to know more abt these kind of functions...may be some link abt functions in sql-2000

help appreciated

cheers

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-12-06 : 06:49:00

check BOL (Books On Line). It was installed along with your SQL SERVER. BOL is the help for SQL SERVER.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-06 : 08:28:28

In your database design, you should make Salary, Bonus and HRA not nullable and include a default constraint to set them each to 0. Then in your DML, you won't need to worry about nullability. Usually best practice is to not allow nulls in the database.

Jay
to here knows when

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources