Sum of 2 columns from different tables

Question

Post reply

Sum of 2 columns from different tables

bpmosullivan

Mr or Mrs. 500

Points: 538
More actions
May 24, 2011 at 7:30 am

#148104

Hi folks,
I need to create a view and add the column (Hours) from Table A & Table B together to make the total sum figure. Any help appreciated.
Table A
Name | Hours
John | 20
John | 0
John | 10
Peter | 30
Peter | 0
Peter | 15
Table B
Name | Hours
John | 10
John | 0
John | 5
Peter | 10
Peter | 0
Peter | 5
I need to add the two columns from Table A & Table B togther so result would be
Name | Hours
John | 45
Peter | 60

Viewing 11 posts - 1 through 10 (of 10 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

This is a simple JOIN and SUM query. Please will you show us what you've tried so far?

John

bpmosullivan Mr or Mrs. 500 Points: 538 More actions · Answer 2

Hi John,

Here is what i've tried so far. I've been googling all day but not getting anywhere

SELECT CAST(SUM(TB1.TimeDuration + TB2.TimeDuration) AS int) AS Total

FROM TB1 CROSS JOIN

TB2

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Almost... you need an INNER JOIN on the Name column.

John

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

bpmosullivan (5/24/2011)
Hi John,
Here is what i've tried so far. I've been googling all day but not getting anywhere
SELECT CAST(SUM(TB1.TimeDuration + TB2.TimeDuration) AS int) AS Total
FROM TB1 CROSS JOIN
TB2

SELECT Person, SUM(TimeDuration) AS TimeDuration FROM (

SELECT Person, TimeDuration FROM dbo.Table1

UNION ALL

SELECT Person, TimeDuration FROM dbo.Table2

) dtTimes

GROUP BY Person

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

John Mitchell-245523 (5/24/2011)
Almost... you need an INNER JOIN on the Name column.
John

Actually you'd need a full outer because you need all data in both tables. So I preffer to user union all instead!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Ah yes - you're right. If you can't be sure that all names appear in both tables then an inner join won't work.

John

bpmosullivan Mr or Mrs. 500 Points: 538 More actions · Answer 7

bpmosullivan

Mr or Mrs. 500

Points: 538

May 24, 2011 at 9:40 am

#1329173

Thanks guys, that works great.

bpmosullivan Mr or Mrs. 500 Points: 538 More actions · Answer 8

bpmosullivan

Mr or Mrs. 500

Points: 538

May 24, 2011 at 9:44 am

#1329179

P.S. What's dtTimes (Alias / Derived table) ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

bpmosullivan (5/24/2011)
P.S. What's dtTimes (Alias / Derived table) ?

Yes, you can't use a derived table unless you name it.

Also from previous test those types of queries perform a little better if you do the group by only at the end instead of group by in both queries and then a 3rd time. That's why I did it like that.

bpmosullivan Mr or Mrs. 500 Points: 538 More actions · Answer 10

bpmosullivan

Mr or Mrs. 500

Points: 538

May 24, 2011 at 10:08 am

#1329209

Many thanks. Much appreciated