merging the results of two queries

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

Transact-SQL (2000)

merging the results of two queries

Author

Topic

djschlie
Starting Member

3 Posts

Posted - 2004-11-17 : 02:29:03

Hi

I have a query which gets similar values from two tables, I want all the avlues that match my query from both table so I use a full outer join:
heres the result:
Date(1) Date(2) Sales$(1) Sales$(2)
NULL 12/12/2003 NULL 123.98
13/12/2003 13/12/2003 124.44 144.22
14/12/2003 NULL 233.22 NULL

theres more but you get the idea
what I want to be able to do is have a total for both sales figure (ie: Sales$(1) + Sales$(2)) for each date, so my output would look something like the following:
Date Sales$
12/12/2003 123.98
13/12/2003 268.66
14/12/2003 233.22

how can i achieve this if there are null values? ive tried straight addition, but if it adds a NULL value, the result is NULL regardless of whether there was a value in the alternative column, can I do SOmething like IF (Sales(1) = NULL), use sales 2, if sales (2) = NULL, use sales(1), otherwise Sales(1) + Sales(2)????????

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-17 : 02:31:08

use isnull or coalesce to change the null value to 0 so you can add them up

--------------------
keeping it simple...

djschlie
Starting Member

3 Posts

Posted - 2004-11-17 : 03:03:18

thanks jen! worked a treat!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-17 : 09:04:15

no full outer joins !!!!!!!

use a union:


select date,sum(amount)
from
  (select date, amount
   from table1
   union all
   select date, amount
   from table2
   ) a
group by date

Do yourself a favor and avoid unreadable and unlogical SQL statement by avoiding full outer and right outer joins -- they are completely unncessary.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources