javamysqlsqlnavicat

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

  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

Solution

  • 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