Search code examples
sqljoinsumamazon-redshiftfull-outer-join

create a table in Redshift by adding columns of the other two tables

I want to create a table in Redshift by adding columns of the other two tables.

Table 1
Table 1 data

Table 2
Table 2 data

Want to create new table on following conditions

  1. if table1.sid = table2.sid
    then t1.totalcorrect+t2.totalcorrect, t1.totalquestions+t2.totalquestions. that is s4 to s7
  2. else data from both tables as it is

Expected output
Output table

Using joins resulting table gives me only S4 to S7 and not other columns as required. Please help me

Solution

  • That's a full join:

    select 
    coalesce(t1.sid, t2.sid) sid, 
    coalesce(t1.totalcorrect,   0) + coalesce(t2.totalcorrect,   0) totalcorrect,
    coalesce(t1.totalquestions, 0) + coalesce(t2.totalquestions, 0) totalquestions
from t1 
full join t2 on t2.sid = t1.sid