Data combination 2

[mattjp]

Hello, I am trying to create a view that sums data from two tables in this fashion:

Table 1:
A 3
B 5
C 1

Table 2:
A 1
C 1
D 3

To produce:
A 4
B 5
C 1
D 3


What is the best way to do this? I have tried using unions, outer joins, but I can't seem to get it to work--I keep running into problems with nulls. Thanks in advance for any help.

 

[PWise]

Select letter ,sum(num)
from
(Select * from
table1
union all
Select * from
table)dt
Group by letter

 

[mattjp]

Thank you very much!!

 

[ousoonerjoe]

I'm sure some else can make it look simpler:

SELECT 
	[IDs] = TabID,
	[Qty] = Quants
INTO #Tmp
FROM Table1

UNION ALL

SELECT 
	[IDs] = TabID,
	[Qty] = Quants
FROM Table2

SELECT
	IDs,
	[Quantity] = SUM(Qty)
FROM #Tmp
GROUP BY IDs
ORDER BY IDs

DROP TABLE #Tmp

--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"

"Don't stop him. He's roll'n."
--------------------------------------------------

 

[gmmastros]

ousoonerjoe,

Take a look at the advice suggested by pwise. In many ways, your advice and his are the similar. Basically, it's a union all for the 2 tables, and then summarize the results. The difference is... you put the summarized data in to a temp table, where pwise didn't.

This is significant from a performance perspective. Both solutions will return the same results, but the advice from pwise will perform better. There was a time when I used a lot of temp tables in my code. I've discovered that it is a performance bottle neck. I mean... sometimes you need to use a temp table, but if you can write a query without it, you're better off. In my experience...

1. Temp tables are 'ok'.
2. Table variables are better then temp tables.
3. Derived tables (the method pwise used) is better than table variables.

Sometimes temp tables have better performance than table variables, but in my experience, most of the time table variables are better.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

ousoonerjoe

does your Even syntax work

 

[ousoonerjoe]

I didn't realize you could put a UNION in the the FROM SELECT statement. I try not to use temp tables if I can/know how to get around it. I figured the temp tables had a little more overhead, but didn't think it was really that much? Of course we all know how fast those seconds add up.

Thanks for the tip, gmmastros.


pwise--yes. Copied and Pasted after verifying the results. Why wouldn't it?


--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"

"Don't stop him. He's roll'n."
--------------------------------------------------

 

[PWise]

Sorry did not that this syntax works
I always use

TabID = IDs ....

learned something new have a Star

 

[PWise]

sorry sb

TabID As IDs ....