0

Here is the table:

enter image description here

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?

0
   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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.