Count the Null Columns in a Row in SQL

Count the Null columns in a row in SQL

This method assigns a 1 or 0 for null columns, and adds them all together. Hopefully you don't have too many nullable columns to add up here...

SELECT 
  ((CASE WHEN col1 IS NULL THEN 1 ELSE 0 END)
  + (CASE WHEN col2 IS NULL THEN 1 ELSE 0 END)
  + (CASE WHEN col3 IS NULL THEN 1 ELSE 0 END)
  ...
  ...
  + (CASE WHEN col10 IS NULL THEN 1 ELSE 0 END)) AS sum_of_nulls
FROM table
WHERE Customer=some_cust_id

Note, you can also do this perhaps a little more syntactically cleanly with IF() if your RDBMS supports it.

SELECT 
  (IF(col1 IS NULL, 1, 0)
  + IF(col2 IS NULL, 1, 0)
  + IF(col3 IS NULL, 1, 0)
  ...
  ...
  + IF(col10 IS NULL, 1, 0)) AS sum_of_nulls
FROM table
WHERE Customer=some_cust_id

I tested this pattern against a table and it appears to work properly.

Count number of NULLs in a row

I'm not sure if there are neater methods, but this should work:

SELECT Field1, Field2, ISNULL(Field1) + ISNULL(Field2) Num_Null
FROM   YourTable;

Test case:

CREATE TABLE YourTable (Field1 varchar(10), Field2 varchar(10));

INSERT INTO YourTable VALUES (NULL, 'A');
INSERT INTO YourTable VALUES ('B', 'C');
INSERT INTO YourTable VALUES ('B', NULL);
INSERT INTO YourTable VALUES (NULL, NULL);

Result:

+--------+--------+----------+
| Field1 | Field2 | Num_Null |
+--------+--------+----------+
| NULL   | A      |        1 |
| B      | C      |        0 |
| B      | NULL   |        1 |
| NULL   | NULL   |        2 |
+--------+--------+----------+
4 rows in set (0.00 sec)

---

UPDATE: Further to the updated question:

If you have columns in your table that look like affiliate_1, affiliate_2, etc, this is rarely a good idea as you would be mixing data with the metadata. In general, a recommended fix is to use another dependent table for the users-to-affiliates relationships, as in the following example:

CREATE TABLE users (
   user_id int, 
   user_name varchar(100),
   PRIMARY KEY (user_id)
) ENGINE=INNODB;

CREATE TABLE users_affiliates (
   user_id int, 
   affiliate_name varchar(100),
   PRIMARY KEY (user_id, affiliate_name),
   FOREIGN KEY (user_id) REFERENCES users (user_id)
) ENGINE=INNODB;

Then sorting the users table by the number of affiliates will look something like this:

SELECT    u.*, d_tb.num_aff
FROM      users
JOIN      (
             SELECT   user_id, COUNT(*) num_aff
             FROM     users_affiliates
             GROUP BY user_id
          ) d_tb ON (d_tb.user_id = u.user_id)
ORDER BY  d_tb.num_aff DESC;

The advantages are plenty, but most importantly it makes queries such as the above easy to write, and flexible enough to work with any number of affiliates (an not limited by the number of columns you allocated).

How to get count of columns that are having null values for a given row in sql?

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;

Count NULL Values from multiple columns with SQL

SELECT COUNT(*)-COUNT(A) As A, COUNT(*)-COUNT(B) As B, COUNT(*)-COUNT(C) As C
FROM YourTable; 

Count null and non-null values in all columns in a table

You can do this -

SELECT COUNT(name) AS name_not_null_count, 
       SUM(CASE WHEN name IS NULL THEN 1 ELSE 0 END) AS name_null_count
FROM table

Approach to calculate null count is: mark all the null records with 1 and take SUM.

Find the record count in each column (exclude blank and null values) of table in entire database

As documented here, sys.partitions.rows "Indicates the approximate number of rows in this partition."

Joining sys.partitions to sys.objects and sys.columns doesn't help because you still have the rows only on partition level.

That said, you're querying the metadata of the database. Doing this, you cannot obtain the counts you need.

What you have to do is execute a query for each table to get the counts for the table's columns. A query to do this for one particular table (e.g. Table1) might be:

SELECT 
   -- col1 would be a string (varchar, char, etc.) column
   count(nullif(col1, '')) as col1_count,
   -- col2 would not be a string column
   count(col2) as col2_count
FROM Table1

The above query leaves you with a result set containing one colum for each original column and one single row containing the counts. You could change the query to use PIVOT to swap columns and rows which would give you your desired structure.

To get what you want for all tables without the manual work, you could use your original query (just the join of sys.objects and sys.columns though) and loop through that query's results using a CURSOR. In this loop you'd generate the queries for all the tables as strings or one big query string with some UNION ALL. These strings/this string would then have to be executed using EXEC()

Example:

DECLARE @MyQuery NVARCHAR(MAX)
SET @MyQuery = 'SELECT ... FROM Table1 UNION ALL SELECT ... FORM Table2 ...'

EXEC(@MyQuery)

Count the number of attributes that are NULL for a row

Possible without spelling out columns. Unpivot columns to rows and count.

The aggregate function count(<expression>) only counts non-null values, while count(*) counts all rows. The shortest and fastest way to count NULL values for more than a few columns is count(*) - count(col) ...

Works for any table with any number of columns of any data types.

In Postgres 9.3+ with built-in JSON functions:

SELECT *, (SELECT count(*) - count(v)
           FROM json_each_text(row_to_json(t)) x(k,v)) AS ct_nulls
FROM   tbl t;

What is x(k,v)?

json_each_text() returns a set of rows with two columns. Default column names are key and value as can be seen in the manual where I linked. I provided table and column aliases so we don't have to rely on default names. The second column is named v.

Or, in any Postgres version since at least 8.3 with the additional module hstore installed, even shorter and a bit faster:

SELECT *,  (SELECT count(*) - count(v) FROM svals(hstore(t)) v) AS ct_nulls
FROM   tbl t;

This simpler version only returns a set of single values. I only provide a simple alias v, which is automatically taken to be table and column alias.

• Best way to install hstore on multiple schemas in a Postgres database?

Since the additional column is functionally dependent I would consider not to persist it in the table at all. Rather compute it on the fly like demonstrated above or create a tiny function with a polymorphic input type for the purpose:

CREATE OR REPLACE FUNCTION f_ct_nulls(_row anyelement)
  RETURNS int  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT (count(*) - count(v))::int FROM svals(hstore(_row)) v';

(PARALLEL SAFE only for Postgres 9.6 or later.)

Then:

SELECT *, f_ct_nulls(t) AS ct_nulls
FROM   tbl t;

You could wrap this into a VIEW ...

db<>fiddle here - demonstrating all

_{Old sqlfiddle}

This should also answer your second question:

... the table name is obtained from argument, I don't know the schema of a table beforehand. That means I need to update the table with the input table name.

---

Related Topics