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
This question is related to
sql
postgresql
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
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;
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
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
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!
Cast the Boolean to an integer and sum.
SELECT count(*),sum(myCol::int);
You get 6,3
.
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);
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;
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
;
Source: Stackoverflow.com