blocks|key|958127|text|SELECT+COALESCE(sum(CASE+WHEN+myCol+THEN+1+ELSE+0+END),0)+FROM+<table+name>|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|958128|或者，正如你自己发现的那样：|unstyled|958129|SELECT+count(CASE+WHEN+myCol+THEN+1+END)+FROM+<table+name>|958130|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>SELECT COALESCE(sum(CASE WHEN myCol THEN 1 ELSE 0 END),0) FROM &lt;table name&gt;
</code></pre>

or, as you found out for yourself:

<pre><code>SELECT count(CASE WHEN myCol THEN 1 END) FROM &lt;table name&gt;
</code></pre>

blocks|key|742468|text|从PostgreSQL+9.4开始，就有了FILTER+clause，它允许非常简洁的查询来计算真值：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|742469|select+count(*)+filter+(where+myCol)
from+tbl;|code-block|syntax|javascript|742470|上面的查询是一个不好的例子，因为一个简单的WHERE子句就足够了，并且只是为了演示语法。FILTER子句的亮点在于它很容易与其他聚合组合在一起：|742471|select+count(*),+--+all
+++++++count(myCol),+--+non+null
+++++++count(*)+filter+(where+myCol)+--+true
from+tbl;|742472|对于使用另一列作为谓词的列上的聚合，该子句特别方便，同时允许在单个查询中提取不同过滤的聚合：|742473|select+count(*),
+++++++sum(otherCol)+filter+(where+myCol)
from+tbl;|742474|entityMap|0|LINK|mutability|MUTABLE|url|http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES^0|L|6|L|D|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@$9|13|A|14|1|15]]|E|$]]|$1|F|3|G|5|H|7|16|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|17|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|18|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|19|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|1A|8|@]|D|@]|E|$I|J]]|$1|S|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]]]

Since PostgreSQL 9.4 there's the <a href="http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES" rel="noreferrer"><code>FILTER</code> clause</a>, which allows for a very concise query to count the true values:

<pre><code>select count(*) filter (where myCol)
from tbl;
</code></pre>

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: 

<pre><code>select count(*), -- all
 count(myCol), -- non null
 count(*) filter (where myCol) -- true
from tbl;
</code></pre>

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:

<pre><code>select count(*),
 sum(otherCol) filter (where myCol)
from tbl;
</code></pre>

blocks|key|958301|text|将布尔值转换为整数并求和。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|958302|SELECT+count(*),sum(myCol::int);|code-block|syntax|javascript|958303|你得到了6,3。|offset|length|style|CODE|958304|entityMap^0|0|0|4|3|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Cast the Boolean to an integer and sum.

<pre><code>SELECT count(*),sum(myCol::int);
</code></pre>

You get <code>6,3</code>.

blocks|key|742344|text|也许，最好的方法是使用nullif函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|742345|总体而言|742346|select
++++count(nullif(myCol+=+false,+true)),++--+count+true+values
++++count(nullif(myCol+=+true,+true)),+++--+count+false+values
++++count(myCol);|code-block|syntax|javascript|742347|或者简而言之|742348|select
++++count(nullif(myCol,+true)),++--+count+false+values
++++count(nullif(myCol,+false)),+--+count+true+values
++++count(myCol);|742349|http://www.postgresql.org/docs/9.0/static/functions-conditional.html|offset|length|742350|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|0|0|1W|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Z|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|10|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|11|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|12|8|@]|9|@$O|13|P|14|1|15]]|A|$]]|$1|Q|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|N]]]]

probably, the best approach is to use nullif function.

in general

<pre><code>select
 count(nullif(myCol = false, true)), -- count true values
 count(nullif(myCol = true, true)), -- count false values
 count(myCol);
</code></pre>

or in short

<pre><code>select
 count(nullif(myCol, true)), -- count false values
 count(nullif(myCol, false)), -- count true values
 count(myCol);
</code></pre>

<a href="http://www.postgresql.org/docs/9.0/static/functions-conditional.html" rel="noreferrer">http://www.postgresql.org/docs/9.0/static/functions-conditional.html</a>

blocks|key|958330|text|最短和最懒惰的(没有强制转换)解决方案是使用以下公式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|958331|SELECT+COUNT(myCol+OR+NULL)+FROM+myTable;|code-block|syntax|javascript|958332|你自己试试吧：|958333|SELECT+COUNT(x+<+7+OR+NULL)
+++FROM+GENERATE_SERIES(0,10)+t(x);|958334|产生的结果与|958335|SELECT+SUM(CASE+WHEN+x+<+7+THEN+1+ELSE+0+END)
+++FROM+GENERATE_SERIES(0,10)+t(x);|958336|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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

<pre><code>SELECT COUNT(myCol OR NULL) FROM myTable;
</code></pre>

Try it yourself:

<pre><code>SELECT COUNT(x &lt; 7 OR NULL)
 FROM GENERATE_SERIES(0,10) t(x);
</code></pre>

gives the same result than

<pre><code>SELECT SUM(CASE WHEN x &lt; 7 THEN 1 ELSE 0 END)
 FROM GENERATE_SERIES(0,10) t(x);
</code></pre>

blocks|key|742154|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|742155|或者可能是这个|unstyled|742156|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;|742157|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>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
</code></pre>

Or Maybe this

<pre><code>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;
</code></pre>

blocks|key|742299|text|在MySQL中，您也可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|742300|SELECT+count(*)+AS+total
+++++,+sum(myCol)+AS+countTrue+--yes,+you+can+add+TRUEs+as+TRUE=1+and+FALSE=0+!!
FROM+yourTable
;|code-block|syntax|javascript|742301|我认为在Postgres中，这是可行的：|742302|SELECT+count(*)+AS+total
+++++,+sum(myCol::int)+AS+countTrue+--convert+Boolean+to+Integer
FROM+yourTable
;|742303|或者更好(为了避免:：并使用标准的SQL语法)：|742304|SELECT+count(*)+AS+total
+++++,+sum(CAST(myCol+AS+int))+AS+countTrue+--convert+Boolean+to+Integer
FROM+yourTable
;|742305|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

In MySQL, you can do this as well:

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

I think that in Postgres, this works:

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

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

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

blocks|key|742500|text|只需将布尔型字段转换为整数并进行求和即可。这将在postgresql上运行：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|742501|select+sum(myCol::int)+from+<table+name>|code-block|syntax|javascript|742502|希望这能有所帮助！|742503|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Simply convert boolean field to integer and do a sum. This will work on postgresql :

<pre><code>select sum(myCol::int) from &lt;table name&gt;
</code></pre>

Hope that helps!

blocks|key|742183|text|SELECT+count(*)+++++++++--+or+count(myCol)
FROM+++<table+name>+++++--+replace+<table+name>+with+your+table
WHERE++myCol+=+true;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|742184|下面是一种使用窗口函数的方法：|unstyled|742185|SELECT+DISTINCT+*,+count(*)+over(partition+by+myCol)
FROM+++<table+name>;

--+Outputs:
--+--------------
--+myCol+%7C+count
--+------%2B-------
--++f++++%7C++2
--++t++++%7C++3
--+++++++%7C++1|742186|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>SELECT count(*) -- or count(myCol)
FROM &lt;table name&gt; -- replace &lt;table name&gt; with your table
WHERE myCol = true;
</code></pre>

Here's a way with Windowing Function:

<pre><code>SELECT DISTINCT *, count(*) over(partition by myCol)
FROM &lt;table name&gt;;

-- Outputs:
-- --------------
-- myCol | count
-- ------+-------
-- f | 2
-- t | 3
-- | 1
</code></pre>

blocks|key|958448|text|select+count(myCol)
from+mytable
group+by+myCol
;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|958449|我会将bool的3种可能状态(false、true、0)分成三行，这在与day之类的另一列组合在一起时特别方便|unstyled|958450|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>select count(myCol)
from mytable
group by myCol
;
</code></pre>
will group the 3 possible states of bool (false, true, 0) in three rows
especially handy when grouping together with another column like day

<pre><code>myCol
------
 true
 true
 true
 false
 false
 null
</code></pre>

In the above table, if I do :

<pre><code>select count(*), count(myCol);
</code></pre>

I get <code>6, 5</code>

I get <code>5</code> 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

postgresql - sql - count of `true` values

PostgreSQL

MySQL

myCol------ true true true false false null在上表中，如果我这样做：select count(*), count(myCol);我得到了6, 5我得到5，因为它不计算null条目。我如何计算真值的数量(在本例中为3)？(这是一个简化，实际上我在count函数中使用了一个复杂得多的表达式)编辑摘要:我还想在查询中包含一个普通计数(*)，所以不能使用where

问postgresql - sql - `true`值的计数
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问postgresql - sql - `true`值的计数EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问postgresql - sql - `true`值的计数
EN