0

I have a table that are having 115 columns. Out of 7 columns I need to get the count of columns that are having not null values for a given row.

Soumendra Mishra
  • 3,483
  • 1
  • 12
  • 38
Deepa Das
  • 43
  • 7
  • Does this answer your question? [Count the Null columns in a row in SQL](https://stackoverflow.com/questions/8596500/count-the-null-columns-in-a-row-in-sql) – Soumendra Mishra Sep 15 '20 at 12:33
  • Deepa Das - If one of the answers for [this question](https://stackoverflow.com/q/65335253/10819573) resolved your issue, you can help the community by marking that as accepted. An accepted answer helps future visitors use the solution confidently. – Arvind Kumar Avinash Dec 18 '20 at 17:23

2 Answers2

1

One method is to use case and +:

select t.*,
       ( (case when col1 is not null then 1 else 0 end) +
         (case when col2 is not null then 1 else 0 end) +
         (case when col3 is not null then 1 else 0 end) +
         (case when col4 is not null then 1 else 0 end) +
         (case when col5 is not null then 1 else 0 end) +
         (case when col6 is not null then 1 else 0 end) +
         (case when col7 is not null then 1 else 0 end)
        ) as cnt_not_nulls_in_row 
from t;

In MySQL, this can be simplified to:

select t.*,
       ( (col1 is not null ) +
         (col2 is not null ) +
         (col3 is not null ) +
         (col4 is not null ) +
         (col5 is not null ) +
         (col6 is not null ) +
         (col7 is not null ) 
        ) as cnt_not_nulls_in_row 
from t;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You may first query the given row from the table using the primary key and the use COUNT to count the number of columns from the queried row having null value, as follows:

WITH derived_row as 
      (SELECT col1, col2, col3, col4, col5, col6, col7 FROM table WHERE primary_key=key)
SELECT COUNT(CASE
                 WHEN col1 IS NULL THEN 1
                 WHEN col2 IS NULL THEN 1
                 WHEN col3 IS NULL THEN 1
                 WHEN col4 IS NULL THEN 1
                 WHEN col5 IS NULL THEN 1
                 WHEN col6 IS NULL THEN 1
                 WHEN col7 IS NULL THEN 1
             END) AS null_column_count
FROM derived_row;
DRaj
  • 11
  • 2