SQL - Merging two queries that have one column difference

I have two SQL queries that gives me this kind of results :

Employee id | Week | SUM(Qty sold)
1             42     2
3             38     15


Employee id | Week | SUM(Qty bought)
1             42     5
2             35     10
3             42     6

When I do a full outer join, I get :

Employee id | Week | SUM(Qty sold) | Employee id | Week | SUM(Qty bought)
1             42     2                1             42     5
2             35     10               null          null   null
3             38     15               null          null   null
null          null   null             3             42     6

But what I'm trying to do but don't know how to is :

Employee id | Week | SUM(Qty sold) | SUM(Qty bought)
1             42     2               5            
2             35     null            10
3             38     15              null
3             42     null            6

The final goal would be to add another column with sold - bought. My real tables are more complicated than that but I think it's easier to understand that way (and also I can't share them).

If anyone can point me in the right direction, I've been plucking my hair all afternoon yesterday over this query.

Thanks

PS: I don't know if that changes anything, but I'm using teradata.

1 answer

  • answered 2019-10-22 08:10 apomene

    You need COALESCE. Try like:

    SELECT COALESCE(t1.EmployeeID, t2.EmployeeID)
        ,COALESCE(t1.Week,.t2.Week)
        ,SUM(Qty sold)
        ,SUM(Qty bought)
    FROM table1 t1
    FULL OUTER JOIN table2 t2 ON t1.EmployeeID= t2.EmployeeID