postgresql - sql - count of `true` values
SqlPostgresqlSql Problem Overview
myCol
------
true
true
true
false
false
null
In the above table, if I do :
select count(*), count(myCol);
I get 6, 5
I get 5
as it doesn't count the null entry.
How do I also count the number of true values (3 in the example)?
(This is a simplification and I'm actually using a much more complicated expression within the count function)
Edit summary: I also want to include a plain count(*) in the query, so can't use a where clause
Sql Solutions
Solution 1 - Sql
SELECT COALESCE(sum(CASE WHEN myCol THEN 1 ELSE 0 END),0) FROM <table name>
or, as you found out for yourself:
SELECT count(CASE WHEN myCol THEN 1 END) FROM <table name>
Solution 2 - Sql
Since PostgreSQL 9.4 there's the FILTER
clause, which allows for a very concise query to count the true values:
select count(*) filter (where myCol)
from tbl;
The above query is a bad example in that a simple WHERE clause would suffice, and is for demonstrating the syntax only. Where the FILTER clause shines is that it is easy to combine with other aggregates:
select count(*), -- all
count(myCol), -- non null
count(*) filter (where myCol) -- true
from tbl;
The clause is especially handy for aggregates on a column that uses another column as the predicate, while allowing to fetch differently filtered aggregates in a single query:
select count(*),
sum(otherCol) filter (where myCol)
from tbl;
Solution 3 - Sql
Cast the Boolean to an integer and sum.
SELECT count(*),sum(myCol::int);
You get 6,3
.
Solution 4 - Sql
probably, the best approach is to use nullif function.
in general
select
count(nullif(myCol = false, true)), -- count true values
count(nullif(myCol = true, true)), -- count false values
count(myCol);
or in short
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
Solution 5 - Sql
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 than
SELECT SUM(CASE WHEN x < 7 THEN 1 ELSE 0 END)
FROM GENERATE_SERIES(0,10) t(x);
Solution 6 - Sql
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 this
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;
Solution 7 - Sql
In MySQL, you can do this as well:
SELECT count(*) AS total
, sum(myCol) AS countTrue --yes, you can add TRUEs as TRUE=1 and FALSE=0 !!
FROM yourTable
;
I think that in Postgres, this works:
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
;
Solution 8 - Sql
Simply convert boolean field to integer and do a sum. This will work on postgresql :
select sum(myCol::int) from <table name>
Hope that helps!
Solution 9 - Sql
SELECT count(*) -- or count(myCol)
FROM <table name> -- replace <table name> with your table
WHERE myCol = true;
Here's a way with Windowing Function:
SELECT DISTINCT *, count(*) over(partition by myCol)
FROM <table name>;
-- Outputs:
-- --------------
-- myCol | count
-- ------+-------
-- f | 2
-- t | 3
-- | 1
Solution 10 - Sql
Benchmark
TL;DR: take the solution you like. There's no significant difference.
Utility scripts
before(){
psql <<-SQL
create table bench (
id serial
, thebool boolean
);
insert into bench (thebool)
select (random() > 0.5)
from generate_series(1, 1e6) g;
analyze bench;
SQL
}
after(){
psql -c 'drop table bench'
}
test(){
echo $(tput bold)$1$(tput sgr0)
psql -c "explain analyze select $1 from bench" | tail -4 | head -2
}
Actual benchmark
Made on a 1.4GHz i5 MacBookPro, psql and pg 12.4 (pg in a linux docker container):
before
test 'count(*) filter (where thebool)'
# Planning Time: 0.138 ms
# Execution Time: 4424.042 ms
test 'count(case when thebool then 1 end)'
# Planning Time: 0.156 ms
# Execution Time: 4638.861 ms
test 'count(nullif(thebool, false))'
# Planning Time: 0.201 ms
# Execution Time: 5267.631 ms
test 'count(thebool or null)'
# Planning Time: 0.202 ms
# Execution Time: 4672.700 ms
test 'sum(thebool::integer)'
# Planning Time: 0.155 ms
# Execution Time: 4602.406 ms
test 'coalesce(sum(case when thebool THEN 1 ELSE 0 END), 0)'
# Planning Time: 0.167 ms
# Execution Time: 4416.503 ms
after
Solution 11 - Sql
select count(myCol)
from mytable
group by myCol
;
will group the 3 possible states of bool (false, true, 0) in three rows especially handy when grouping together with another column like day