Difficulty in getting count of a simple query

Question

I have a table with 1118 records. In these records there is one field flag which is by default NULL. 556 of these records have field flag marked as true while the rest are NULL. When I run the following query :

select count(*) from table_name where flag!='true'

I get result as 0. Where as actual result should be 1118-556 i.e 562.

What Am I doing wrong?

Use tinyint to define bool case fields as well as 1 and 0, 0 for null cases and 1 for true. — Prix, Apr 20 '12 at 10:03

score 2 · Accepted Answer · edited May 23 '17 at 10:30

NULL never compares to anything, not even another NULL

select count(*) from table_name where flag!='true' OR flag IS NULL

or

select count(*) from table_name where flag IS NULL

It depends if you have another value apart from true (false?) as well as NULLs

select
    count(CASE WHEN flag = 'true' THEN flag END) AS truecount,
    count(CASE WHEN flag = 'false' THEN flag END) AS falsecount
    count(*) - count(flag) AS NULLCount
from 
    table_name

Finally, see these to understand COUNT and null/not null values

Worked like a charm. Thanks a lot for your help. Really appreciate it :) — Deepak, Apr 20 '12 at 10:22

score 0 · Answer 2 · answered Apr 20 '12 at 10:04

0

In Queries NULL will never be matched by any boolean expression unless mentioned explicit:

SELECT COUNT(*) FROM TABLE_NAME WHERE flag IS NULL

answered Apr 20 '12 at 10:04

bardiir

14,556
9
41
66

Difficulty in getting count of a simple query

2 Answers2