SQL to join two views

Question

Need some help joining these two tables

I have two views that looks like this

view1                view2
+------+--------+    +------+--------+
| code | SUM(*) |    | code | SUM(*) |
+------+--------+    +------+--------+
| AAA  |      4 |    | AAA  |      4 |
| BBB  |      3 |    | CCC  |      1 |
+------+--------+    +------+--------+

I want to join them into a table that looks like this

+------+--------+
| code | SUM(*) |
+------+--------+
| AAA  |      4 |
| BBB  |      3 |
| CCC  |      1 |    
+------+--------+ 

I have tried, but only failed..

Solution 1

For your first result, the answers posted using union will do the trick:

select * from view1
union
select * from view2

However, given the fact that one of your columns is a sum, this seems unlikely to be what you actually want.

For your second result (where the values are added), you'll need to use a union and a subquery:

select
    code,
    sum(yourcol)

from
(
    select
        code,
        yourcol

    from view1

    union all

    select
        code,
        yourcol

    from view2
) source

group by code

Solution 2

You can use a UNION for this:

SELECT * FROM view1
UNION DISTINCT
SELECT * FROM view2