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
| improve this answer | |
  • while this may answer the question, it's generally more helpful to explain why instead of just providing code in an answer. – Max Vernon Feb 4 at 20:00
  • the way I understand this, the case function inserts 1 or 0 in each column, and adding them up gives the total missing values across all the columns – bwrr Feb 5 at 8:48

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.