none
count number of null values in a table based on a column value RRS feed

  • Question

  • Hi folks

    here is my table

    event_id, server_time, value_a, value_b, value_c, API

          01,         t1,             a1,      b1,          c1,     , API1

          null,        T2,            null,    b2,           c2,    , API2

          02,        null,           a3,       null,        c3,     , API1

          03,        null,          null,      b3,         null,     , API1

    what I want to do is count the number of null values in each of the columns, but for value_b null will be counted only if API= API1 and

    value_c null value will be counted only of API = API2.

    appreciate the help here 


    Sunday, April 14, 2019 5:02 PM

All replies

  • Do you want count NULL values or or all values? Your subject line says one thing, the body text something else. I will go by the subject line.

    SELECT SUM(CASE WHEN value_a IS NULL THEN 1 ELSE 0 END),
           SUM(CASE WHEN value_b IS NULL AND API = 'API1' THEN 1 ELSE 0 END),
           SUM(CASE WHEN value_c IS NULL AND API = 'API2' THEN 1 ELSE 0 END)
    FROM   yourtable


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, April 14, 2019 6:45 PM
  • For counting non-null values with API logic

    SELECT count(value_a)
    	,count(coalesce(value_b,IIF(API = 'API1','x',NULL)))
    	,count(coalesce(value_c,IIF(API = 'API2','x',NULL)))
    FROM sampletable;

    Monday, April 15, 2019 2:54 AM
  • I want to count number of null values
    Monday, April 15, 2019 3:03 AM
  • HI skskBi1

    SELECT SUM(IIF(event_id IS NULL, 1, 0)) AS cntevent_id, 
           SUM(IIF(server_time IS NULL, 1, 0)) AS cntserver_time, 
           SUM(IIF(value_a IS NULL, 1, 0)) AS cntvalue_a, 
           SUM(IIF(value_b IS NULL
                   AND API = 'API1', 1, 0)) AS cntvalue_b, 
           SUM(IIF(value_c IS NULL
                   AND API = 'API2', 1, 0)) AS cntvalue_c
    FROM(VALUES
    (1, 
     't1', 
     'a1', 
     'b1', 
     'c1', 
     'API1'
    ),
    (NULL, 
     'T2', 
     NULL, 
     'b2', 
     'c2', 
     'API2'
    ),
    (2, 
     NULL, 
     'a3', 
     NULL, 
     'c3', 
     'API1'
    ),
    (3, 
     NULL, 
     NULL, 
     'b3', 
     NULL, 
     'API1'
    )) AS dt(event_id, server_time, value_a, value_b, value_c, API);

    Hope it can help you.

     

    Best Regards,

    Natig


    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. 


    Monday, April 15, 2019 5:23 AM
  • CREATE TABLE mytable(
       event_id    INTEGER   
      ,server_time VARCHAR(12)
      ,value_a     VARCHAR(16)
      ,value_b     VARCHAR(11)
      ,value_c     VARCHAR(13)
      ,API         VARCHAR(5)
      
    );
    INSERT INTO mytable(event_id,server_time,value_a,value_b,value_c,API ) VALUES
     (01,'t1','a1','b1','c1', 'API1')
    ,(NULL,'T2',NULL,'b2','c2', 'API2')
    ,(02,NULL,'a3',NULL,'c3', 'API1')
    ,(03,NULL,NULL,'b3',NULL, 'API1');
    
    
    
    
    Select 
    SUM(CASE WHEN event_id IS NULL THEN 1 ELSE 0 END) event_id_Nullcnt,
    SUM(CASE WHEN server_time IS NULL THEN 1 ELSE 0 END) server_time,
    SUM(CASE WHEN value_a IS NULL THEN 1 ELSE 0 END) value_a_Nullcnt,
    SUM(CASE WHEN value_b IS NULL THEN 1 ELSE 0 END) value_b_Nullcnt,
    SUM(CASE WHEN API='API2' and value_c IS NULL THEN 1 ELSE 0 END) value_c_Nullcnt_API2 
    
    from mytable
    
    
    drop table mytable

    Monday, April 15, 2019 2:40 PM
    Moderator