CODEWITHSUNDEEP
sqlsql-servert-sqlselect
AToya
AToya

Reputation: 575

Count of non-null columns in each row

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

Upvotes: 38

Views: 49429

Answers (4)

dsp9107
dsp9107

Reputation: 21

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;

Upvotes: 2

roman
roman

Reputation: 117345

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

Upvotes: 47

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

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

Upvotes: 30

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

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;

Upvotes: 4

Related Questions