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

use sum() instead. count just checks if there's something that's countable (e.g. not null). 0 counts exactly the same as 1. — Marc B
– Marc B, Commented Oct 26, 2015 at 19:22
Ah, thanks. Do you know if it's possible to sum all 3 columns without a subquery? — simplycoding
– simplycoding, Commented Oct 26, 2015 at 19:25
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) Simple + do the job — Lukasz Szozda
– Lukasz Szozda, Commented Oct 26, 2015 at 19:29
sum doesn't accept multiple arguments. do sum(a+b+c), instead of sum(a,b,c) — Marc B
– Marc B, Commented Oct 26, 2015 at 19:31

Community · Accepted Answer · 2017-05-23 11:52:16Z

4

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

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Oct 26, 2015 at 19:24

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Comment

deadbabykitten Over a year ago

count is an aggregate function. It COUNTs the number of rows returned, not their values. SUM adds all the VALUEs of the rows. Can't edit because there are too many pending edits.

klin · Accepted Answer · 2015-10-26 19:33:57Z

3

You can use simple cast to integer:

select
    sum(facebook::int) facebook,
    sum(instagram::int) instagram,
    sum(twitter::int) twitter,
    sum(facebook::int+ instagram::int+ twitter::int) total
from public.sampletable;

answered Oct 26, 2015 at 19:33

klin

123k15 gold badges241 silver badges263 bronze badges

3 Comments

Clodoaldo Neto Over a year ago

This is much simpler than the cumbersome case syntax in the accepted answer.

Lukasz Szozda Over a year ago

Good point, casting data for true -1 false - 0 is good approach. I allowed myself update my answer too. Anyway +1 :)

Lukasz Szozda Over a year ago

@ClodoaldoNeto I am not from Postgresql world so I would use approach I suggested. The point of view dependent of what are you familliar with for example with MySQL you don't need to do anything: sqlfiddle.com/#!9/e1836/1/0. Anyway I also wanted to answer why OP original query doesn't work

Collectives™ on Stack Overflow

Using CASE WHEN on boolean columns

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related