Number of NULL samples per row

Question

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

Example:

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

UPDATE: I want this query to sort based on the amount of Affiliates sales of this book. Thus, the presence of 3 branches will be sorted higher than 2, regardless of which of them. There are about seven branches in my database, and they can grow. Therefore, any request that requires each Affiliate field is likely to be too long.

Table:

Affiliates_Cache - the primary key Affiliate_ISBN, has book prices on various branches (NULL if it is not available). Affiliates_Cache is the one where I want to count the number of NULL

Answer 1

I'm not sure if there are more accurate 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: In addition to the updated question:

If you have columns in the table that look like affiliate_1 , affiliate_2 , etc., this is rarely a good idea, as you will mix data with metadata. In general, the recommended fix is ​​to use another dependent table for the relationship between users and branches, 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 many, but most importantly, it makes queries, such as those described above, easy to write and flexible enough to work with any number of branches (not limited by the number of selected columns).

Answer 2

How about this request? (Turning to the test case of 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:

 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;