Count missing values across columns and join back to original table Here is the table:



I want to count the missing values across each row for t1, t2, t3,..., and create another column in the same table with the results as shown in the picture. 

I can easily do this in something like python pandas. But on SQL (specifically BigQuery and/or postgreSQL, MySQL), I can't seem to figure out the syntax. Here is my attempt:

select
  array_agg(id),
  array_agg(date),
  count(t)
from
(
  select id, date, t1 as t from testdata
  union all
  select id, date, t2 as t from testdata 
  union all
  select id, date, t3 as t from testdata   
) as data
group by id, date, t


Any ideas where I am going wrong? I think union all unpivots the wide to long table, but how do I count the missing values within a specific range and join all the results back to the original table?
SELECT id, date, t1, t2, t3
     CASE WHEN t1 IS NULL THEN 1 ELSE 0 END 
     + CASE WHEN t2 IS NULL THEN 1 ELSE 0 END 
     + CASE WHEN t3 IS NULL THEN 1 ELSE 0 END AS missing_across_all_cols
   FROM testdata