postgresql - sql - `true` 值的计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5396498/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql - sql - count of `true` values
提问by EoghanM
myCol
------
true
true
true
false
false
null
In the above table, if I do :
在上表中，如果我这样做：
select count(*), count(myCol);
I get 6, 5
我得到 6, 5
I get 5as it doesn't count the null entry.
我得到，5因为它不计算空条目。
How do I also count the number of true values (3 in the example)?
我还如何计算真值的数量（示例中为 3）？
(This is a simplification and I'm actually using a much more complicated expression within the count function)
（这是一个简化，我实际上在 count 函数中使用了一个更复杂的表达式）
Edit summary: I also want to include a plain count(*) in the query, so can't use a where clause
编辑摘要：我还想在查询中包含一个普通的 count(*)，所以不能使用 where 子句
回答by Daniel
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>
回答by Dwayne Towell
Cast the Boolean to an integer and sum.
将布尔值转换为整数并求和。
SELECT count(*),sum(myCol::int);
You get 6,3.
你得到6,3。
回答by Ilja Everil?
Since PostgreSQL 9.4 there's the FILTERclause, which allows for a very concise query to count the true values:
从 PostgreSQL 9.4 开始有FILTER子句，它允许一个非常简洁的查询来计算真值：
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:
上面的查询是一个不好的例子，因为一个简单的 WHERE 子句就足够了，并且仅用于演示语法。FILTER 子句的亮点在于它很容易与其他聚合组合：
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;
回答by wrobell
probably, the best approach is to use nullif function.
可能，最好的方法是使用 nullif 函数。
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
http://www.postgresql.org/docs/9.0/static/functions-conditional.html
回答by Le Droid
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);
回答by Jaspreet Singh
Simply convert boolean field to integer and do a sum. This will work on postgresql :
只需将布尔字段转换为整数并求和。这将适用于 postgresql ：
select sum(myCol::int) from <table name>
Hope that helps!
希望有帮助！
回答by Kuberchaun
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;
回答by ypercube??
In MySQL, you can do this as well:
在 MySQL 中，您也可以这样做：
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:
我认为在 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):
或更好（避免 :: 并使用标准 SQL 语法）：
SELECT count(*) AS total
, sum(CAST(myCol AS int)) AS countTrue --convert Boolean to Integer
FROM yourTable
;
回答by vol7ron
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
