How to count only missing/blank values in char data type column

Question

My table is:

date       value       name
may02       22          s1
may03                   s1
may04       34          s2
may05       35          s2
may06                   s3
may07                   s3
may08       22          s3

Value is in char data type. My query is

select count(*) as missing_value from my_table  where value= “ “ group by name;

The above query is not working properly I need to calculate only MISSING /BLANK count in Value column grouped by Name

O/P should be

name       missing_value
s1           1
s2           0
s3           2

Lukasz Szozda · Accepted Answer · 2015-11-02 10:47:11Z

2

You can use CASE WHEN:

SELECT name,
  COUNT(CASE WHEN `value` IS NULL OR TRIM(`value`) = '' THEN 1 ELSE NULL END) 
  AS missing_value 
FROM my_table 
GROUP BY name;

SqlFiddleDemo

or (this will skip groups that don't have any empty/blank data though):

SELECT name,
  COUNT(*) as missing_value 
FROM my_table 
WHERE `value` IS NULL OR TRIM(`value`) = ''
GROUP BY name;

edited Nov 2, 2015 at 10:47

answered Nov 2, 2015 at 10:32

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Collectives™ on Stack Overflow

How to count only missing/blank values in char data type column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related