Count the number of existing values in a field in a record

Question

1. Such as I have a table of User---(id,name,age,hobby,sports)
2. Then it has a record that (1,"zhangsan",18,null,null);
3. Next you can see the total of record is 3, because hobby and sports are null.

use SQL sentences to count the number of existing values in a field in a record

Help and thanks

Answer 1

Use CASE expressions to count each column.

SELECT
    CASE WHEN Column1 IS NOT NULL THEN 1 ELSE 0 END
    + CASE WHEN Column2 IS NOT NULL THEN 1 ELSE 0 END
    -- repeat for all columns
    AS NumberOfNonNullColumns
FROM MyTable