Using CASE WHEN on boolean columns

Question

This is Postgres 8.x. Speicifcally Redshift.

Table has all FALSE in all the 3 columns. However, when I run this query, it returns as if all are TRUE.

SELECT
count(CASE WHEN facebook THEN 1 ELSE 0 END)
, count(CASE WHEN instagram THEN 1 ELSE 0 END)
, count(CASE WHEN twitter THEN 1 ELSE 0 END)
FROM public.sampletable

Not sure what I'm doing wrong. I've also tried CASE WHEN facebook = TRUE

I've made sure the cells aren't NULL or anything as well

Answer 1

The problem is that COUNT count 1 and 0 the same. One way is to change the values you want to avoid to NULL

SELECT
  count(CASE WHEN facebook  THEN 1 ELSE NULL END)
, count(CASE WHEN instagram THEN 1 ELSE NULL END)
, count(CASE WHEN twitter   THEN 1 ELSE NULL END)
FROM public.sampletable

SELECT
   count(CASE WHEN facebook  THEN 1 END) -- ELSE NULL is default so you can skip
  ,count(CASE WHEN instagram THEN 1 END)
  ,count(CASE WHEN twitter   THEN 1 END)
FROM public.sampletable

or as Marc B proposed in comment use SUM:

SELECT
 SUM(CASE WHEN facebook THEN 1 ELSE 0 END)
,SUM(CASE WHEN instagram THEN 1 ELSE 0 END)
,SUM(CASE WHEN twitter THEN 1 ELSE 0 END)
FROM public.sampletable

EDIT:

If you need combined sum you can use:

SUM(CASE WHEN facebook THEN 1 ELSE 0 END +
    CASE WHEN twitter THEN 1 ELSE 0 END +
    CASE WHEN instagram THEN 1 ELSE 0 END)

EDIT 2

This answer is inspired by answer below. You can simply cast data:

SELECT SUM(CAST(facebook AS INT)),
       SUM(CAST(instagram AS INT)),
       SUM(CAST(twitter AS INT)),
       SUM(CAST(facebook AS INT) + CAST(instagram AS INT) + CAST(twitter AS INT))
FROM sampletable;

SqlFiddleDemo