Count "" (Empty Strings) on Same Row

Question

I have a table like this.

id ___ d1 ___ d2 ___ d3 ___ d4 ___ d5 ___ d6 ___ d7 ___ d8
----------------------------------------------------------
01 ___ XX ___ "" ___ "" ___ "" ___ "" ___ "" ___ "" ___ "" (There are 7 ""s)
02 ___ XX ___ XX ___ XX ___ "" ___ "" ___ "" ___ "" ___ "" (There are 5 ""s)
03 ___ XX ___ XX ___ "" ___ "" ___ "" ___ "" ___ "" ___ "" (There are 6 ""s)
04 ___ XX ___ "" ___ "" ___ "" ___ "" ___ "" ___ "" ___ "" (There are 7 ""s)

I want to see an output like this:

id ___ count
------------
01 ___ 7
02 ___ 5
03 ___ 6
04 ___ 7

How can I do that? Simply, I want to count empty strings but on same row.

These are empty strings, not NULLs, right?

Sergey Kalinichenko
– Sergey Kalinichenko

2014-05-11 13:46:41 +00:00
Commented May 11, 2014 at 13:46 — Sergey Kalinichenko
– Sergey Kalinichenko, Commented May 11, 2014 at 13:46

Sergey Kalinichenko · Accepted Answer · 2014-05-11 13:48:38Z

2

You can use a case statement, and add up the results:

SELECT
    id
,   (    CASE d1 WHEN '' THEN 1 ELSE 0 END
    +    CASE d2 WHEN '' THEN 1 ELSE 0 END
    +    CASE d3 WHEN '' THEN 1 ELSE 0 END
    +    CASE d4 WHEN '' THEN 1 ELSE 0 END
    +    CASE d5 WHEN '' THEN 1 ELSE 0 END
    +    CASE d6 WHEN '' THEN 1 ELSE 0 END
    +    CASE d7 WHEN '' THEN 1 ELSE 0 END
    +    CASE d7 WHEN '' THEN 1 ELSE 0 END
    ) as TotalSpaces
FROM myTable

answered May 11, 2014 at 13:48

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-05-11 14:41:14Z

1

I would be inclined to use the MySQL shortcut that treats a boolean as an integer:

select id,
       ((d1 = '') + (d2 = '') + (d3 = '') + (d4 = '') + (d5 = '') +
        (d6 = '') + (d7 = '') + (d8 = '')
       ) as "Count"
from table t;

answered May 11, 2014 at 14:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Count "" (Empty Strings) on Same Row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related