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

Question

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

Table 1

Table 2

Want to create new table on following conditions

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

Expected output

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

score 2 · Accepted Answer · answered Sep 14 '20 at 09:23

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

score 1 · Answer 2 · answered Sep 14 '20 at 10:47

There are two ways to do this, and I'm not sure which would be faster in Redshift. One is union all and group by:

select sid, sum(totalcorrect) as totalcorrect, sum(totalquestions) as totalquestions
from ((select sid, totalcorrect, totalquestions
       from t1
      ) union all
      (select sid, totalcorrect, totalquestions
       from t2
      )
     ) t
group by sid;

The second uses full join, for which I recommend using the using clause:

select sid,
       coalesce(t1.totalcorrect, 0) + coalesce(t2.totalcorrect, 0) as totalcorrect,
       coalesce(t1.totalquestions, 0) + coalesce(t2.totalquestions, 0) as totalquestions
from t1 full join
     t2
     using (sid);

There are differences between these two approaches. The first guarantees one row per sid in the result set, even if there are duplicates in one of the tables. The first also combines NULL values of sid into a single row.

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

2 Answers2