How to count non null fields?

Question

I need to get the count of fields where the value is not null.

My table

   city      id_no  no1    no2    no3
   chn       A12    2158
   chn       A13    8181   8182  8183
   chn       A14    19138

I need to get the count of fields set for no1, ..., no3

My query

SELECT 
  count(id_no) as total_id,
  (count(no1) +
    count(no2) +
    count(no3)) as c_count
FROM table 
  WHERE city='chn';

My output

  total_id      c_count
  3             9

Expected:

  total_id      c_count
  3             5

I am expecting 5 instead of 9, since 5 fields are not null.

`COUNT` doesn't count `NULL` values. Make sure the value in these columns is in fact, really `NULL`, and not an empty string (is it an integer field?) — Bart Friederichs, Sep 01 '16 at 06:53
Possible duplicate of [Count the Null columns in a row in SQL](http://stackoverflow.com/questions/8596500/count-the-null-columns-in-a-row-in-sql) — Micha Wiedenmann, Sep 01 '16 at 07:03

score 0 · Answer 1 · answered Sep 01 '16 at 06:59

SELECT 
count(id_no) as total_id,
(case when count(no1)='' or count(no1) is null then 0 else count(no1) end +
    case when count(no2)='' or count(no2) is null then 0 else count(no2) end +
    case when count(no3)='' or count(no3) is null then 0 else count(no3) end +
    case when count(no4)='' or count(no4) is null then 0 else count(no4) end +
    case when count(no5)='' or count(no5) is null then 0 else count(no5) end +
    case when count(no6)='' or count(no6) is null then 0 else count(no6) end +
    case when count(no7)='' or count(no7) is null then 0 else count(no7) end +
    case when count(no8)='' or count(no8) is null then 0 else count(no8) end  +
    case when count(no9)='' or count(no9) is null then 0 else count(no9) end +
    case when count(no10)='' or count(no10) is null then 0 else count(no10) end
     ) as c_count
    FROM table 
    WHERE city='chn';

score 0 · Answer 2 · answered Sep 01 '16 at 07:01

0

I am getting same output as you want please check here i provide my screenshot

answered Sep 01 '16 at 07:01

krunal nerikar

436
4
12

score 0 · Answer 3 · answered Sep 01 '16 at 07:05

0

SELECT count(id_no) as total_id,
        count(CASE WHEN `nol`!="" THEN 1 END) as no1 
 FROM `table` where city='chn'

try like this

answered Sep 01 '16 at 07:05

premi

93
10

score 0 · Accepted Answer · answered Sep 01 '16 at 07:06

0

OR you can simply do this to avoid NULL or ' ' data

SELECT 
  count(id_no) as total_id,
  (count(CASE WHEN no1 > 0 THEN no1 ELSE NULL END) +
   count(CASE WHEN no2 > 0 THEN no2 ELSE NULL END) +
   count(CASE WHEN no3 > 0 THEN no3 ELSE NULL END)) as c_count
FROM table 
WHERE city='chn';

answered Sep 01 '16 at 07:06

Shushil Bohara

5,556
2
15
32

Thk u S Bohara, i got the output – Yogasuganya Sukumar Sep 01 '16 at 09:54

Mak · Answer 5 · 2016-09-01T07:57:21.137

0

select count(distinct a.`id_no`),count(*) from 
(
  select `id_no`,`no1` as `non`  from table WHERE city='chn'
  union all
  select `id_no`,`no2` as `non`  from table WHERE city='chn'
  union all
  select `id_no`,`no3` as `non`  from table WHERE city='chn'
)a where a.`non` is not null

edited Sep 01 '16 at 07:57

answered Sep 01 '16 at 07:14

Mak

154
4

How to count non null fields?

5 Answers5