Adding two columns from two different tables

Question

I have two tables with same field types. Fields that are common in here are *BG_ID* and Store Number. Now I want to SUM two columns (*total_area* and *total_area_blk*) from two tables and group them by BG_ID .

The following is what I tried which doesnt work:

 select t.bg_id, t.store_number,sum(a.total_area),sum(b.total_area_blk)
 from (select bg_id,store_number,total_area
 from temp_Prop_area_block a
 where store_number='33665'
 union all
 select bg_id,store_number,total_area_blk 
 from temp_Prop_area_BG b
 where store_number='33665')

Maheswaran Ravisankar · Accepted Answer · 2014-01-15 17:05:46Z

3

Hope, this helps you!

SELECT bg_id,  store_number,  SUM(total_area)
FROM
  (
    SELECT bg_id,  store_number,  total_area as total_area
    FROM temp_Prop_area_block a
    WHERE store_number='33665'
    UNION ALL
    SELECT bg_id,   store_number,  total_area_blk as total_area
    FROM temp_Prop_area_BG b
    WHERE store_number='33665'
  ) my_view
GROUP BY bg_id, store_number;

edited Jan 15, 2014 at 17:05

answered Jan 15, 2014 at 16:57

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nathan Over a year ago

area fields are different in two tables . one is total_area and other is total_area_blk

Maheswaran Ravisankar Over a year ago

When we do union, the resultset will be clubbed and the column alias will turn up as the first UNION part's column name.. So now, total_area itself will do, and it holds both values.. Added alias to my query for readability!

Nathan Over a year ago

This one returns no output.

Maheswaran Ravisankar Over a year ago

Just try to run the Inner Sub Query only and see the results.. Reason should be with your WHERE condition.

Nathan Over a year ago

This also doesnt return any output.

|

Collectives™ on Stack Overflow

Adding two columns from two different tables

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related