summing columns - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

summing columns

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2010-05-05 : 01:47:11

Hi All,

I want to sum up columns along with nulls.

create table #temp1
(
usage nvarchar(100),
usagenon nvarchar(100)
)

insert into #temp1 values('20',null)
insert into #temp1 values(null,'30')
insert into #temp1 values(null,null)

select usage, usagenon, usage+usagenon from #temp1

need output as:
20 NULL 20
NULL 30 30
NULL NULL NULL

Regards,
Sachin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-05 : 02:01:52

[code]
select usage, usagenon, isnull(usage, 0) + isnull(usagenon, 0)
from #temp1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-05 : 02:17:57

quote:
Originally posted by sachingovekar

Hi All,

I want to sum up columns along with nulls.

create table #temp1
(
usage nvarchar(100),
usagenon nvarchar(100)
)

insert into #temp1 values('20',null)
insert into #temp1 values(null,'30')
insert into #temp1 values(null,null)

select usage, usagenon, usage+usagenon from #temp1

need output as:
20 NULL 20
NULL 30 30
NULL NULL NULL

Regards,
Sachin

Should that usage+usagenon be an addition or concat?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-05 : 02:19:26

quote:
Originally posted by khtan

select usage, usagenon, isnull(usage, 0) + isnull(usagenon, 0)
from #temp1

KH
[spoiler]Time is always against us[/spoiler]

That will not give the wanted output like shown in third row of example output.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-05 : 02:21:03

quote:
Originally posted by webfred

quote:
Originally posted by sachingovekar

Hi All,

I want to sum up columns along with nulls.

create table #temp1
(
usage nvarchar(100),
usagenon nvarchar(100)
)

insert into #temp1 values('20',null)
insert into #temp1 values(null,'30')
insert into #temp1 values(null,null)

select usage, usagenon, usage+usagenon from #temp1

need output as:
20 NULL 20
NULL 30 30
NULL NULL NULL

Regards,
Sachin

Should that usage+usagenon be an addition or concat?

No, you're never too old to Yak'n'Roll if you're too young to die.

Ah I see - sorry.
The title is summing...

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-05 : 02:21:08

quote:
Originally posted by webfred

quote:
Originally posted by khtan

select usage, usagenon, isnull(usage, 0) + isnull(usagenon, 0)
from #temp1

KH
[spoiler]Time is always against us[/spoiler]

That will not give the wanted output like shown in third row of example output.

No, you're never too old to Yak'n'Roll if you're too young to die.

Damn ! Looks like it is time to change my specs

the data type is string not integer. My eyes can't see the single quote at all

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-05 : 02:24:44

use convert() to convert to integer and then SUM it.


select usage, usagenon, isnull(convert(int, usage), 0) + isnull(convert(int, usagenon), 0)
from   #temp1

Unless Sachin wanted a concatenation rather than a SUM ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-05 : 02:27:37

quote:
Originally posted by webfred
That will not give the wanted output like shown in third row of example output.

No, you're never too old to Yak'n'Roll if you're too young to die.

Missed this also.


select usage, usagenon, 
       case when isnull(usage, usageon) is not null
       then isnull(convert(int, usage), 0) + isnull(convert(int, usagenon), 0)
       else NULL
       end
from   #temp1

KH
[spoiler]Time is always against us[/spoiler]

sachingovekar
Posting Yak Master

101 Posts

Posted - 2010-05-05 : 02:28:10

the third row should have a null. 0 means a value for my report and null means no value.

need output as:
20 NULL 20
NULL 30 30
NULL NULL NULL

Regards,
Sachin

sachingovekar
Posting Yak Master

101 Posts

Posted - 2010-05-05 : 02:30:00

Thanks KH, got your missed query it worked.

Many thanks again.
Sachin

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-05 : 02:31:12

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources