CODEWITHSUNDEEP
sqlpostgresqlcountboolean
Javier
Javier

Reputation: 31

How can I count the number of row instances where a value from any of the columns is set to true

How can I count the number of rows where any of the column fields is true? or How can I count the number of users where any of the columns is set to true?

Example:

User    Name    Last Name
1   TRUE    TRUE
2   FALSE   TRUE
3   TRUE    FALSE
4   FALSE   FALSE
5   FALSE   FALSE

For the example above the number of rows or users where the Name or Last Name is true, is equal to 3 (User 1, User 2, and User 3)

Upvotes: 0

Views: 297

Answers (2)

achref
achref

Reputation: 1220

You can Achieve your goal using this query

SELECT COUNT(*) FROM [table] WHERE [row1] = true OR [row2] = true;

Upvotes: 0

user330315
user330315

Reputation:

You can use an IN condition:

select count(*)
from the_table
where true in (name, last_name)

This is equivalent to:

select count(*)
from the_table
where name = true 
   or last_name = true;

where name = true or last_name = true can be shortened to where name or last_name

But the first query is easier to adapt when more columns should be included e.g. true in (name, last_name, nick_name)

To get the number of columns that are true, you can use a trick in Postgres where casting a boolean to a number yields 1 or 0 so to get the number of columns that are true just add them up:

select name::int + last_name::int as "Number of true values"
from the_table

SQLFiddle example: http://sqlfiddle.com/#!15/23a4a/1

Upvotes: 1

Related Questions