CODEWITHSUNDEEP
mysqlsql
rockstardev
rockstardev

Reputation: 13527

MySQL Query to get row with least amount of data?

Let's say I have this:

id | col1 | col2 | col3 | col4 | col5 | name
1  | xxx  | xxx  | xxx  |      | xxx  | John
2  |      |      |      | xxx  | xxx  | Peter
3  | xxx  | xxx  | xxx  |      |      | Sam

How would I get a result like this:

id | filledData | name
1  | 4          | John
2  | 2          | Peter
3  | 3          | Sam

Basically, there are set columns that I want to use to determine how "well populated" the data is, and then sort them by that, so that I can try and fill the columns with the least amount of data first?

Upvotes: 0

Views: 92

Answers (2)

John Woo
John Woo

Reputation: 263723

You just need to check if the column is null or not using CASE statement.

SELECT  ID,
        name,
        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 filledData
FROM    tableName

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This requires a complicated case statement:

select id, filledData, name
from (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)
             ) as filledData
      from t
     ) t

Upvotes: 0

Related Questions