set @t1=(select sum(a) from order where type='1') + (select sum(b) from order2 where code='3')

Question

Post reply

set @t1=(select sum(a) from order where type='1') + (select sum(b) from order2 where code='3')

joe 82532

Newbie

Points: 2
More actions
October 7, 2010 at 6:55 am

#227744

How can I combine the select clauses so the if order type='1' has a value of 2 and there are no order2 code='3' the answer will be 2 not null. if order2 code='3' has a value of 4 then I get the correct result of 6.
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Is it me or there's a better way to describe this problem?

I don't think I understand what you're after.

Can you explain?

-- Gianluca Sartori

WayneS SSC Guru Points: 95461 More actions · Answer 2

try:

set @t1=IsNull((select sum(a) from order where type='1'),0) + IsNull((select sum(b) from order2 where code='3'),0)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

WayneS (10/7/2010)
try:
set @t1=IsNull((select sum(a) from order where type='1'),0) + IsNull((select sum(b) from order2 where code='3'),0)

... ok, it was me! 🙂

-- Gianluca Sartori

joe 82532 Newbie Points: 2 More actions · Answer 4

Thanks. That did it.

I was close to what you had but must have missed a period somewhere and we all know what happens when that happens.;-)

WayneS SSC Guru Points: 95461 More actions · Answer 5

Gianluca Sartori (10/7/2010)
WayneS (10/7/2010)
try:
set @t1=IsNull((select sum(a) from order where type='1'),0) + IsNull((select sum(b) from order2 where code='3'),0)
... ok, it was me! 🙂

:w00t::hehe::-P

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2