Query to count how many fields equal to 0 a record has

Asked

Viewed 32 times

0

I need to return how many fields equal to 0 each record has and rank the one with the most zeros down. Example:

id   | col2 | col3 | col4
1    | 1    | 0    | 0
2    | 0    | 0    | 0
3    | 0    | 1    | 4
4    | 0    | 3    | 5
5    | 3    | 2    | 40

The return in this case should be:

id  | count(0)
5   | 0
3   | 1
4   | 1
1   | 2
2   | 3
mysql sql query

  • What is your SQL???

  • https://stackoverflow.com/questions/31590705/count-how-many-columns-have-a-specific-value

  • 1

    @Pedrohenrique, I appreciate the effort, but I cannot use queries with sub query, because my table has many fields and many records. The cost in performance is high. Also, there is no APPLY in Mysql.

1 answer

1


You can use the function SUM and compare each column if the column is equal to 0 you put 1, or you put 0:

(SELECT id, SUM(IF(col2 = 0, 1, 0) + IF(col3 = 0, 1, 0) + IF(col4 = 0, 1, 0)) AS qtde FROM sua_tabela) ORDER BY qtde

  • Perfect. Besides not having sub query involved, I only had to change grouping so "group by id" and sort by "id" asc.

Browser other questions tagged mysql sql query

You are not signed in. Login or sign up in order to post.