CODEWITHSUNDEEP
mysqlsql
Atul Singh
Atul Singh

Reputation: 13

How to count the number of columns with blank or 0 values

I am new to mysql . Please help me to solve the below requirement.

Table example :-

--------+-----------+-----------+--------+---------+
Id.     | fname | lname | qid1 | qid2 | 
--------+-----------+-----------+--------+---------+
1       | atul. |       |    0 |  0   |
--------+-----------+-----------+--------+---------+-

I want the answer as "3" of 1st id because 'lname' is blank and 'qid1', 'qid2' are 0.

Upvotes: 0

Views: 75

Answers (2)

sagi
sagi

Reputation: 40481

So basically you want the count for each Id of how many columns are null ,blank or 0?

SELECT T.id,
       ((t.fname is null or t.fname ='')
        +(t.lname is null or t.lname = '')
        +(t.qid1 is null or t.qid1 = 0)
        +(t.qid2 is null or t.qid2 = 0)) as cnt
FROM YourTable t

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You can use case in most databases; however, MySQL provides a shortcut where you can just add the boolean expressions:

select t.*,
       ((fname is null or fname = '') +
        (lname is null or lname = '') +
        (qid1 is null or qid1 = '') +
        (qid2 is null or qid2 = '')
       ) as numBlankOrNull
from t;

Upvotes: 2

Related Questions