Count number of NULLs in a row

Question

Is there a way to get a column indicating the number of NULL fields in a row? This would be within a SELECT statement.

For Example:

Field1  Field2  Num_Null
-----------------------
NULL     "A"      1

UPDATE: I want this query so I can sort based on how many Affiliates sales there are of a given Book. So having 3 affiliates would be sorted higher than having 2, regardless of which ones. There are about seven affiliates in my database, and that's subject to grow. So any query requiring that each Affiliate field be specified would probably be too long

The table:

Affiliates_Cache - Primary key is Affiliate_ISBN, has the prices of the book on various affiliates (NULL if its not available). Affiliates_Cache is the one where i want to count the number of NULLs

Answer 1

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).

Answer 2

How about this Query ? (Referring to the Test case given by Daniel.)

SELECT Field1, Field2, (2 - (COUNT(ALL Field1)+COUNT(ALL Field2)))  Num_Null
FROM   @YourTable
GROUP BY Field1, Field2

Answer 3

Keep it simple and Standard:

SELECT Field1, Field2, 
       CASE WHEN Field1 IS NULL THEN 1 ELSE 0 END
       + CASE WHEN Field2 IS NULL THEN 1 ELSE 0 END
       AS Num__Null
  FROM YourTable;

Test case in full:

WITH YourTable (Field1, Field2)
     AS 
     (
      SELECT CAST(Field1 AS VARCHAR(10)), 
             CAST(Field2 AS VARCHAR(10))
        FROM (
              VALUES (NULL, 'A'),
                     ('B', 'C'),
                     ('B', NULL),
                     (NULL, NULL)
             ) AS YourTable (Field1, Field2)
     )
SELECT Field1, Field2, 
       CASE WHEN Field1 IS NULL THEN 1 ELSE 0 END
       + CASE WHEN Field2 IS NULL THEN 1 ELSE 0 END
       AS Num__Null
  FROM YourTable;