Postgresql - sql - number of true values

Question

myCol ------ true true true false false null 

In the above table, if yes:

 select count(*), count(myCol); 

I get 6, 5

I get 5 because it does not account for null entry.

How to also count the number of true values ​​(3 in the example)?

(This is a simplification, and I actually use a much more complex expression in the count function)

Edit Resume: I also want to include a simple request (*) in the request, so I cannot use the where clause

Answer 1

 SELECT COALESCE(sum(CASE WHEN myCol THEN 1 ELSE 0 END),0) FROM <table name> 

or, as you know yourself:

 SELECT count(CASE WHEN myCol THEN 1 END) FROM <table name> 

Answer 2

Probably the best approach is to use the nullif function.

at all

 select count(nullif(myCol = false, true)), -- count true values count(nullif(myCol = true, true)), -- count false values count(myCol); 

or shorter

 select count(nullif(myCol, true)), -- count false values count(nullif(myCol, false)), -- count true values count(myCol); 

http://www.postgresql.org/docs/9.0/static/functions-conditional.html

Answer 3

Pass Boolean numbers to integer and sum.

 SELECT count(*),sum(myCol::int); 

You get 6,3 .

Answer 4

Since PostgreSQL 9.4 contains a FILTER article , which allows a very concise query to calculate true values:

 select count(*) filter (where myCol) from tbl; 

The above query is a bad example that a simple WHERE clause will suffice and is intended only to demonstrate syntax. Where the FILTER condition is shining is that it easily combines with other units:

 select count(*), -- all count(myCol), -- non null count(*) filter (where myCol) -- true from tbl; 

The proposal is especially convenient for aggregates in a column that uses another column as a predicate, while allowing you to get only filtered aggregates in one query:

 select count(*), sum(otherCol) filter (where myCol) from tbl; 

Answer 5

The shortest and laziest (without casting) solution would be to use the formula:

 SELECT COUNT(myCol OR NULL) FROM myTable; 

Try it yourself:

 SELECT COUNT(x < 7 OR NULL) FROM GENERATE_SERIES(0,10) t(x); 

gives the same result as

 SELECT SUM(CASE WHEN x < 7 THEN 1 ELSE 0 END) FROM GENERATE_SERIES(0,10) t(x); 

Answer 6

 select f1, CASE WHEN f1 = 't' THEN COUNT(*) WHEN f1 = 'f' THEN COUNT(*) END AS counts, (SELECT COUNT(*) FROM mytable) AS total_counts from mytable group by f1 

Or maybe it's

 SELECT SUM(CASE WHEN f1 = 't' THEN 1 END) AS t, SUM(CASE WHEN f1 = 'f' THEN 1 END) AS f, SUM(CASE WHEN f1 NOT IN ('t','f') OR f1 IS NULL THEN 1 END) AS others, SUM(CASE WHEN f1 IS NOT NULL OR f1 IS NULL THEN 1 ELSE 0 END) AS total_count FROM mytable; 

Answer 7

In MySQL, you can also do this:

 SELECT count(*) AS total , sum(myCol) AS countTrue --yes, you can add TRUEs as TRUE=1 and FALSE=0 !! FROM yourTable ; 

I think this works in Postgres:

 SELECT count(*) AS total , sum(myCol::int) AS countTrue --convert Boolean to Integer FROM yourTable ; 

or better (to avoid :: and use standard SQL syntax):

 SELECT count(*) AS total , sum(CAST(myCol AS int)) AS countTrue --convert Boolean to Integer FROM yourTable ; 

Answer 8

 SELECT count(*) -- or count(myCol) FROM <table name> -- replace <table name> with your table WHERE myCol = true; 

You can use the Windowing function here:

 SELECT DISTINCT *, count(*) over(partition by myCol) FROM <table name>; -- Outputs: -- -------------- -- myCol | count -- ------+------- -- f | 2 -- t | 3 -- | 1 

Answer 9

Just convert the boolean field to integer and do the sum. This will work on postgresql:

 select sum(myCol::int) from <table name> 

Hope this helps!