Count of non-null columns in each row

Question

I have a table that contains 4 columns and in the 5th column I want to store the count of how many non-null columns there are out of the previous 4. For example:

Where X is any value:

Column1 | Column2 | Column3 | Column4 | Count
  X     |    X    |   NULL  |    X    |   3
 NULL   |   NULL  |    X    |    X    |   2
 NULL   |   NULL  |   NULL  |   NULL  |   0

Why do you want that ? When do you want that ? Do you want that filled out automatically ? — yoozz, Aug 12 '13 at 17:51

Roman Pekar · Accepted Answer · 2015-03-04T06:44:26.493

45

select
    T.Column1,
    T.Column2,
    T.Column3,
    T.Column4,
    (
        select count(*)
        from (values (T.Column1), (T.Column2), (T.Column3), (T.Column4)) as v(col)
        where v.col is not null
    ) as Column5
from Table1 as T

edited Mar 04 '15 at 06:44

answered Aug 12 '13 at 18:09

Roman Pekar

107,110
28
195
197

@GuilhermeCamposHazan it does, but it could be that it will not work with columns of different types – Roman Pekar Nov 27 '15 at 12:34
hey @RomanPekar, I've never seen that syntax before where you can pull values of column from the same table and rename a table with parenthesis and then call where a table is not null? can you help elaborate what's going on there? – mlh351 May 23 '18 at 16:51
1

Well, it's actually standard SQL. You can replace `values` with `select ... union all` if you find it more readable. So basically, for each row we create a fake 'table' with rows containing data from chosen columns and then just run aggregation query on it – Roman Pekar May 24 '18 at 09:00
Yes @romanpekar. This doesn't work where the types are different. I have a date and int in the table and it doesn't seem to pull. – Axwack Nov 26 '21 at 14:31

Giannis Paraskevopoulos · Answer 2 · 2013-08-12T18:01:47.600

28

SELECT   Column1,
         Column2,
         Column3,
         Column4,
         CASE WHEN Column1 IS NOT NULL THEN 1 ELSE 0 END + 
         CASE WHEN Column2 IS NOT NULL THEN 1 ELSE 0 END + 
         CASE WHEN Column3 IS NOT NULL THEN 1 ELSE 0 END + 
         CASE WHEN Column4 IS NOT NULL THEN 1 ELSE 0 END AS Column5
FROM     Table

edited Aug 12 '13 at 18:01

answered Aug 12 '13 at 17:49

Giannis Paraskevopoulos

18,261
1
49
69

score 4 · Answer 3 · answered Aug 12 '13 at 17:58

SELECT Column1, Column2, Column3, Column4,
  Column5 = LEN(COALESCE(LEFT(Column1,1),'')) 
          + LEN(COALESCE(LEFT(Column2,1),''))
          + LEN(COALESCE(LEFT(Column3,1),'')) 
          + LEN(COALESCE(LEFT(Column4,1),''))
 FROM dbo.YourTable;

Demo:

DECLARE @x TABLE(a VARCHAR(32),b INT,c VARCHAR(32),d VARCHAR(32));

INSERT @x VALUES
('01',3023,NULL,'blat'),
('02',NULL, NULL,'blat'),
('03',5,NULL,'blat'),
('04',24,'bo','blat'),
(NULL, NULL, NULL, NULL);

SELECT a, b, c, d,
    LEN(COALESCE(LEFT(a,1),'')) 
  + LEN(COALESCE(LEFT(b,1),''))
  + LEN(COALESCE(LEFT(c,1),'')) 
  + LEN(COALESCE(LEFT(d,1),''))
 FROM @x;

what I like about this answer is that it's going to work for columns of different types while I can imagine situation when my answer is going to fail for certain combinations of types / values in the columns — Roman Pekar, Nov 09 '19 at 19:19

dsp9107 · Answer 4 · 2021-10-01T08:53:16.750

That would require you to run an UPDATE query for the Count column every time any of the rest of the columns are updated or maybe you could set up a trigger if your DB enables you to.

If you're looking to create such a view then the following query would do just fine,

SELECT
  Column1,
  Column2,
  Column3,
  Column4,
  (
    COALESCE((CASE WHEN Column1 IS NOT NULL THEN 1 ELSE 0 END), 0)
    + COALESCE((CASE WHEN Column2 IS NOT NULL THEN 1 ELSE 0 END), 0)
    + COALESCE((CASE WHEN Column3 IS NOT NULL THEN 1 ELSE 0 END), 0)
    + COALESCE((CASE WHEN Column4 IS NOT NULL THEN 1 ELSE 0 END), 0)
  ) AS Count
FROM some_table;

Count of non-null columns in each row

4 Answers4

Linked