Columns sum issue if not exists

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

Columns sum issue if not exists

Author

Topic

anujpratap84
Starting Member

45 Posts

Posted - 2011-12-01 : 00:10:10

Hi All,

I have 3 tables.

I want to get sum of a column from all table.
Like
SELECT a.ABC+b.ABC+c.ABC FROM a INNER JOIN b
ON a.month = b.month INNER JOIN c
ON b.month = c.month
It's working fine if all tables have values .
if any table do not have a single row then query always return empty not sum of rest two.
Please help.

Anuj Pratap Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 00:25:39

[code]
SELECT COALESCE(a.ABC,0)+COALECSE(b.ABC,0)+COALESCE(c.ABC,0) FROM a INNER JOIN b
ON a.month = b.month INNER JOIN c
ON b.month = c.month
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 00:26:33

reason is NULL is not regarded as a value under default condition so any operations involving NULL values will only return NULL as result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-12-01 : 04:19:30

Hi,

Refer following link to understand the effects of nulls on inner and outer joins.
http://www.techrepublic.com/article/understand-the-effects-of-nulls-on-inner-and-outer-joins/6138783

anujpratap84
Starting Member

45 Posts

Posted - 2011-12-01 : 05:17:01

Thanks Visakh..

quote:
Originally posted by visakh16

reason is NULL is not regarded as a value under default condition so any operations involving NULL values will only return NULL as result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Anuj Pratap Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 05:18:02

quote:
Originally posted by anujpratap84

Thanks Vikram..

quote:
Originally posted by visakh16

reason is NULL is not regarded as a value under default condition so any operations involving NULL values will only return NULL as result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Anuj Pratap Singh

You renamed me?

Anyways welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources